Formula Help - Exclude Holidays
Hi, I am trying to edit the formula below, to make the formula not could holidays (It is also set to not count weekends/Today). How would I include it to not count holidays also?
=IF(DATEONLY(TODAY()) = [Requested Due Date]@row, 0, NETWORKDAY(TODAY(), [Requested Due Date]@row))
Answers
-
I'm note sure but I think that holidays listed in the sheet setting are not included in NETWORKDAYS - you will need to test this or have it confirmed by someone else.
If true you could just add holidays in the sheet settings.
-
There's an optional piece of the NETWORKDAYS function that allows you to add in Holiday Dates to exclude from the formula, see: NETWORKDAYS function
NETWORKDAYS( start_date, end_date, [ holidays ])
This is in [these] to indicate it's optional.
What I've seen most members do is set up a hidden Date column in their sheet titled Holidays, where they list all the dates that they want to formula to skip. Then you include that range at the end of your formula, like so:
=IF(DATEONLY(TODAY()) = [Requested Due Date]@row, 0, NETWORKDAYS(TODAY(), [Requested Due Date]@row, Holiday:Holiday))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I agree with @Genevieve P..
I have also sometimes created a separate sheet specifically for holiday dates and use a cross sheet reference. This makes it easier if you have to use the same list of holidays across multiple sheets.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!