Using the holiday parameter in workday function

Lisa Vercellone

=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

Paul Newcome

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.

Paul Newcome

Happy to help. 👍️

Paul Newcome

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.

Lisa Vercellone

Thanks for the answer. Good to know when designing reports and such!

Paul Newcome

Happy to help. 👍️

Kevin7859

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.

Paul Newcome

@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.

Lisa Vercellone

That's how I have ours set up. A 'Holidays' SmartSheet that I reference.