Help

Timesheet Hours Difference For Payroll in Quarter Hour Increments

Topic Labels: Formulas
1061 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Darryl_Johnson
4 - Data Explorer
4 - Data Explorer

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
JT_Kirk
5 - Automation Enthusiast
5 - Automation Enthusiast

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