CODEWITHSUNDEEP
excelexcel-formulaexcel-dates
goodnoodle
goodnoodle

Reputation: 3

Identify if a date range includes Weekends and/or Holidays in Excel

I'm trying to figure out how to identify if a date range contains weekends or holidays. For example:

Name Start Date End Date Check
John Aug 22, 2023 Aug 26, 2023
Jane Jul 1, 2023 Jul 8, 2023
Bill Aug 28, 2023 Aug 30, 2023
Bob Sep 18, 2023 Sep 22, 2023
Tim Dec 25, 2023 Dec 28, 2023
Tom Feb 27, 2024 Mar 1, 2024
Dan Feb 28, 2024 Mar 3, 2024

In this case, I would want to know that the rows John, Jane, Tim, and Dan all have dates that include weekends. In the check field, ideally it would say Pass or Fail depending if the date includes weekends and/or holidays or not.

I've tried the WORKDAY function but can't get it to consider the holidays.

=IF( WORKDAY(B2, (C2-B2), H2:H11)=C2, "Pass", "Fail")

I used H2:H11 as a list of holiday dates. Not sure if I'm listing them correctly. Can anyone help here? Also, how do I hard code dates as serial values instead of referring to the H2:H11 range?

Also, I want to make sure if the start date or end date is a holiday/weekend, it also marks as Fail.

Thanks, in advance.

Upvotes: 0

Views: 988

Answers (0)

Related Questions