Let’s say you need to classify a series of dates as either Weekday, Weekend or Holiday. Here’s a formula and a custom function you can try.
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.
If you make the changes that I show to your A2 cell references (note the required plus signs), then the following single formula will spill the output for the entire range of dates…
=IF(COUNTIF(G:G,A2:A31),”Holiday”,”Week”&IF(NETWORKDAYS(+A2:A31,+A2:A31),”day”,”end”))
While not any better than your formula, I thought you and your reader might find this alternative formula (again, single formula spilling entire output) to be of interest…
=IF(COUNTIF(G:G,A2:A31),”Holiday”,”Week”&MID(“dayend”,1+3*(WEEKDAY(A2:A31,2)>5),3))
As a follow up… is there any kind of code I can wrap around formulas I post to you so that normal quote marks are not converted to the stylized ones that are now showing in the formulas above?
Sorry Rick when I try to edit your comment the quotation marks look ok – but when they display they look wrong.
I use the Akismet plug-in to control spam in comments and it has to do with how Akismet displays the text.
Interesting that the quote marks “straighten out” when you edit my comment. The only reason I asked is because if someone copy/pastes either of the two formulas I posted in my original reply, the formulas will not work… you have to replace each quote mark with a “normal” quote mark in order to make them work.
Sorry – it is frustrating, I will see if i can find a solution.