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 information? 👀 | 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
- 66.7K Get Help
- 438 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 509 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi! I cannot figure this out. I have a "% Complete" column (0, 25, 50, 75, 90, 100, Cancelled). I made a simple checkbox column that should be checked if the item's "% Complete" value is equal to anyt…
- This formula keeps coming up as #UNPARSEABLE. I have checked the name of the columns, they are correct. Any ideas? I am trying to show the days remaining in a project. =IF([% Complete]@row = 1, "", NE…
- Hi All! I´m trying to find the error in this formula but have not been able to find the solution after many attempts. Any ideas? =IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF(…
