Help

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

Timesheet Hours Difference For Payroll in Quarter Hour Increments

Topic Labels: Formulas
735 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

1 Reply 1

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)
   )
 )