Excel Day Type Custom Function

In the image below we have dates in column A and the formulas in columns B to E.

Column B displays the day of the date in column A.

Column G has three holiday dates for January. One of them is a Sunday. Holidays take precedent over weekends. So 26 Jan needs to display Holiday.

The formula for the day of the week in cell B2 is. All the formulas shown below have been copied down.

=TEXT(A2,"ddd")

The more complex formula for the day type in cell C2 is.

=IF(COUNTIF(G:G,A2),"Holiday","Week"&IF(NETWORKDAYS(A2,A2),"day","end"))

The first argument if the IF function returns 1 if the date is in column G. It returns zero if the date isn’t in column G. Excel converts 1 into TRUE and zero into FALSE. If the date exists in column G the “Holiday” is returned.

If the date isn’t in column G, then the second IF function uses the NETWORKDAYS function as its first argument. This returns 1 if the day is a weekday and zero if the day is a weekend. Again, Excel converts 1 to TRUE and zero to FALSE. The text “day” or “end” is returned by the IF function and joined to “Week” to return the correct day type.

Custom Function

We can convert this formula into a custom function and test it using the following LAMBDA function – cell D2.

=LAMBDA(dte,holidays,IF(COUNTIF(holidays,dte),"Holiday","Week"&IF(NETWORKDAYS(dte,dte),"day","end")))(A2,G:G)

The final custom function in use is shown in cell E2.

The definition of the custom function is shown below.