Hi everyone! Kindly asking for your help for formula in airtable.
I’m trying to keep track of our team’s first response time and completion time duration for our Airtable tickets but we would like to exclude the count for non-working hours, weekends and holidays; our working hours will only be from Monday to Friday, from 8:00AM to 5:00PM GMT +8.
Currently, I’ve already set it up like this:
First Response Time duration in minutes
DATETIME_DIFF({First Response Time},{Opened date & time (PHT)}, 'minutes')
and Overall completion Time duration in minutes
DATETIME_DIFF({Resolution Time},{First Response Time}, 'minutes')
This is it looks like at the moment.
Hoping for help from anyone! thank you in advance!
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 holidayfield- 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
- 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
- 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!
