Jul 04, 2022 10:10 PM
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!
Jul 04, 2022 11:49 PM
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
Is public holiday
field
Is public holiday?
formulaFrom 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!