Statutory Holiday

Ontario Admin
Ontario Admin ✭✭
edited 07/20/22 in Formulas and Functions

Hi All,

I hope to get some guidance on a formula to use.

I have a scheduled date ranges in which to provide a service at it. But I need to check if the shift/period has a state holiday! and give the result as "Yes" or "No" in a third column called "Service was provided During the Holiday"!

The 2 ranges of date are "Coverage Start Date" and "Coverage End Date". 

For example:

Coverage Start Date: June 29, 2022

Coverage End Date: July 05, 2022

The result should be "Yes" as July 01, 2022, is a holiday.

I added it manually for now.


  • Mary_A
    Mary_A ✭✭✭✭✭✭
    edited 07/20/22

    We do something similar. You need a sheet like "Holidays" which lists all the state holidays and when they occur. Then, you would reference this sheet in your "Service was provided during a holiday" column. --- basically your formula checks are the dates in these rows found in this external sheet range and return Yes or No.

    The advantage of this architecture is that you can change the source sheet as Holidays change or are added.

    There are already a couple of answers around writing date range formulas in the community. Do you need help with the formula in addition to thinking of how to architect a solution?

  • Ontario Admin
    Ontario Admin ✭✭
    edited 07/20/22

    Thank you so much for your help, and yes please, I would appreciate your assistance with the formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!