Using the holiday parameter in workday function

Lisa Vercellone
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


Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!