Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Identify Date as Workday or Weekend/Holiday

0

I have thousands of dates in a file that grows consistently.  The tab/worksheet is named "Data Census."  I would like a separate column within the "Data Census" tab that notes if a date is a "Workday" or "Weekend/Holiday"  I currently have a formula written as =IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"Weekend/Holiday","Workday").  

Within the same file, I have a tab/worksheet labeled as "Holidays Multi Year" which notes multiple years of dates our organization recognizes major US federal holidays.  I have the holiday date in Column D and have it noted in column E as "Weekend/Holiday".  

How can I add to my formula on the "Data Census" tab to also look at my "Holidays Multi Year" tab and identify Monday through Friday days as "Weekend/Holiday" where appropriate?  

Thank you for your time and help.  

exceldate-time
MS Office Version: 2016
posted by: JustDebbie5 on Fri Nov 14, 2025 at 1:45 pm
Answer
Discuss

Discussion

Hello JustDebbie5,
It's been a few days and you have not chosen either John's or mine answer; nor have you provided any feedback (discussion) on our proposed answers. It would be greatly appreciated if you would.
Also, I have another idea for a slightly different approach but I need clarification of a few things before I move forward on it.
1) That you provided feedback on what John and I have already presented
2) The worksheet "Holidays Multi Year" has a list of holiday dates in column "D" - correct?
3) Since the date is on the "Holidays Multi Year" worksheet it is already a known holiday. It might be more beneficial for column "E" to identify which day of the week it is (ie: Sunday, Wednesday, Friday, etc.)
4) If the date in "A2" ("Data Census") is not listed (not a holiday) in column "D" of the "Holidays Multi Year" worksheet, why not have the formula return "Not Found" ?
5) Mon-Fri should only be identified as "Workday"; Sat and Sun should be identified as "Weekend". No date needs to be identified as "Holiday" because any date on "Holidays Multi Year" worksheet is a holiday.
6) Is there an empty cell near "A2" where the formula can be entered?  

Any additional details will be appreciated.
WillieD24 (rep: 723) Nov 17, '25 at 6:09 pm
@Willie

Just me saying well done for all your efforts to help this user. Once again it looks like a thankless task, sadly. Better luck next time (to both of us!)
John_Ru (rep: 6792) Nov 19, '25 at 5:09 pm
@John

I was thingking the same thing. A poster comes looking for help, gets help, but doesn't bother to comment or give thanks. It is discouraging, but, I will continue to offer help whenever I can (just like you do).
In this case it is Debbie who is missing out. I have put together an even better and cleaner solution. But, Debbie won't see it because she has not provided any feedback for us.

Cheers   :-)
WillieD24 (rep: 723) Nov 19, '25 at 11:38 pm
Add to Discussion

Answers

0

Hi JustDebbie5 and welcome to the Forum.

You didn't upload a file but I think you just need to use the NETWORKDAYS function in Excel. Look at Don's tutorial here: Get the Number of Workdays Between Two Dates in Excel

posted by: John_Ru (rep: 6792) on Fri Nov 14, 2025 at 7:01 pm
Discuss

Discussion

P.s. if that works for you, please remember to mark the Answer above as Selected. Thanks. 
John_Ru (rep: 6792) Nov 14, '25 at 7:04 pm
I realise now that I misread the Question (missing the requirement for text in a separate column) but hope:

1) you can use the revised Answer from WillieD24

2) you can use NETWORKDAYS in any  calculations of periods of working days (so not need an If formula using that new text column). 
John_Ru (rep: 6792) Nov 16, '25 at 3:16 am
Add to Discussion
0

Hello JustDebbie5 and welcome to the forum,

You haven't uploaded a sample file so I can't give any specific cell references. You already have the holidays identified on the "Holidays Multi Year" worksheet so this should be quite easy. You could achieve this by modifying your "IF" formula to include a "VLOOKUP" component. The formula would "lookup" the date on the "Data Census" worksheet and find it on the "Holidays Multi Year" worksheet. If the date is found it returns the value from column E of the "Holidays Multi Year" worksheet. If the date is not found then it returns "Workday".

It would look something like this:

IF(VLOOKUP(A2,'Holidays Multi Year'!D1:E500,1,FALSE) = A2, "Weekend/Holiday", "Workday")

Change D1:E500 to the range that matches your range on the "Holidays Multi Year" worksheet.

A key thing is that the date in A2 and column D must be of the same format.

Update Nov. 15 @ 01:15

Another possible formula would be:   (see attached file)

=IF(IFNA(VLOOKUP(A2,Holidays Multi Year!D1:E500,1,FALSE),"Not found") = "Not Found", "Workday", "Weekend/Holiday")

In this formula the "IFNA" part checks to see if the VLOOKUP returns a #NA result. If the result is #NA (date is not found) then the "IF" formula returns "Workday" else it returns "Weekend/Holiday".

Update #2 - Nov. 15 @ 16:10

This can be further reduced to:

IF(ISNA(VLOOKUP(C5,Holiday!D3:D8,1,FALSE)),"Workday", "Weekend/Holiday")

If this is the solution you were looking for then please mark my answer as Selected.

Cheers :-)

posted by: WillieD24 (rep: 723) on Fri Nov 14, 2025 at 7:14 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login