Statutory Holiday

Ontario Admin

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

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

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