Hi Jane, I’ve put something together here that I think does what you’re looking for
This setup will allow you to filter on the
Should be included column, where any records that have a
Date value that falls into the requirements below have a “Yes” value
- Monday - Friday
- Not on a list of public holidays set within the formula found in the
Is public holiday field
- In the screenshot, we can see that the record for “5 July 2022” is marked as a public holiday. This is because I’ve set it as such in the
Is public holiday? formula
- Between 8AM to 5PM GMT+8
From what I understand, when referencing values in date fields, the calculations will always assume that it’s GMT +0, and so you’ll notice that in a couple of the formulas I’ve had to put in
DATEADD(Date, 8, "hours") occasionally
To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.
Let me know if you have any questions!