Using the holiday parameter in workday function
=IF(Runs@row = "Daily", WORKDAY([Completed Date]@row, 1, {Holidays Range 1}), IF(Runs@row = "weekly", WORKDAY([Completed Date]@row - (WEEKDAY([Completed Date]@row) - 2), 5, {Holidays Range 1}), IF(Runs@row = "Monthly", WORKDAY(DATE(YEAR([Completed Date]@row), MONTH([Completed Date]@row) + 1, 1) - 1, 1, {Holidays Range 1}))))
I've been using the above function and it's been working beautifully until now. The weekly portion of the function is set so that no matter when I complete the task, it will set the next due date to the following Monday. I added the holiday parameter with the intention that it would skip any holidays when creating the next due date if it fell on that holiday. With the 4th being on a Tuesday, it has set my next due date to the following Tuesday rather than Monday. I thought I could get around it by setting the completed date to after the holiday (today the 5th) and it would set the next due date to Monday the 10th but it is still setting it to Tuesday the 11th.
Does the holiday parameter skip a holiday if it is anywhere in the date range? I thought it would just skip it if the date fell on a holiday. I'm trying to understand as I have used the holiday parameter throughout various workflows.
Thanks, Lisa
Best Answers
-
It considers the holiday date similar to how it considers a weekend date. So if there is one holiday within the date range, it will add an extra calendar day similar to if there is a weekend within the range it will add two calendar days.
-
Happy to help. 👍️
Answers
-
It considers the holiday date similar to how it considers a weekend date. So if there is one holiday within the date range, it will add an extra calendar day similar to if there is a weekend within the range it will add two calendar days.
-
Thanks for the answer. Good to know when designing reports and such!
-
Happy to help. 👍️
-
How can this be used for non- specific holidays. Such as the time schools are closed in certain states for Thanksgiving week or Christmas break of 2 weeks.
-
@Kevin7859 You can have a separate sheet that lists down a column every date that is to be considered a holiday and then reference this column with a cross sheet reference.
-
That's how I have ours set up. A 'Holidays' SmartSheet that I reference.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!