Skip to main content

Timesheet Hours Difference For Payroll in Quarter Hour Increments

  • March 12, 2019
  • 1 reply
  • 27 views
D

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.

This topic has been closed for replies.

1 reply

J
  • JT_Kirk
  • New Participant
  • March 13, 2019

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)
   )
 )
Terms & ConditionsAccessibility statement