Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Mar 12, 2019 12:27 PM
I am trying to put a formula in Airtable that would calculate the difference between two times and give me the result in quarter hour increments. I would import the data as follows from a spreadsheet:
Name Start Date End Date Start Time End Time Calculated Difference between two
Doe, John 3/1/2019 3/2/2019 8:45 pm 7:00 am 10.25 Hours
Smith, Jane 3/1/2019 3/2/2018 8:35 pm 7:00 am 10.25 Hours (Rounded to 8:45 pm)
What would the formula be for the calculated duration because my payroll company is set up the enter into quarter of hour increments.
Your help is greatly appreciated.
Mar 13, 2019 12:48 PM
Here is a formula you can use. Update the column name to match yours.
IF(OR(T({Stop Time}) = blank(), T({Start Time}) = blank()),
"",
CONCATENATE(
DATETIME_DIFF({Stop Time}, {Start Time}, 'hours'),
":",
MOD((ROUND((DATETIME_DIFF({Stop Time}, {Start Time}, 'minutes')+7.5)/15) * 15), 60)
)
)