How can I change this formula to exclude holidays?
I have set up a project status report to automate expected deadline dates for certain project deadlines. My current formula excludes the weekends, but not holidays. What can I change/add to this formula to do so?
=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1), "")
Also, if I need to have the dates counted by 4 weeks vs days how might this be changed? Currently am converting 4 weeks into # of days to follow the existing formula.
(Project tracker is formatted to read left to right across columns rather than top to bottom with hierarchy rows.)
Best Answers
-
Hi @vao
You can add the holidays to the WORKDAY function. Either by including them within DATE functions or creating a list in a date formatted column and referencing that column.
This would exclude Jan 1, 2024
=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,DATE(2025, 1, 1)), "")
This would exclude all dates in a column called Holiday
=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,Holiday:Holiday), "")
If you put the Holidays in a separate sheet you can use a cross sheet reference instead. That would look something like
=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,{Holidays}), "")
If your worksheet is set to a 5 day working week, to convert days into weeks you divide the output by 5:
=(IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,Holiday:Holiday), ""))/5
-
Thank you all for your help! I was able to create a Holidays column within my sheet. This is my new formula:
=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1, Holidays:Holidays), "")
Answers
-
You would use the third field within the WORKDAY function. The below linked article includes examples on how to exclude a single holiday or a range of holidays.
.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi @vao
You can add the holidays to the WORKDAY function. Either by including them within DATE functions or creating a list in a date formatted column and referencing that column.
This would exclude Jan 1, 2024
=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,DATE(2025, 1, 1)), "")
This would exclude all dates in a column called Holiday
=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,Holiday:Holiday), "")
If you put the Holidays in a separate sheet you can use a cross sheet reference instead. That would look something like
=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,{Holidays}), "")
If your worksheet is set to a 5 day working week, to convert days into weeks you divide the output by 5:
=(IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,Holiday:Holiday), ""))/5
-
Thank you all for your help! I was able to create a Holidays column within my sheet. This is my new formula:
=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1, Holidays:Holidays), "")
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Looks great! Well done!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!