A way to calculate over hours

Topic Labels: Formulas
1785 2
Showing results for 
Search instead for 
Did you mean: 

I have 2 date fields. A ‘Book in’ and a ‘Book out’ that logs the date and time someone shows up to work and leaves. Then I use DATETIME_DIFF({Book out}, {Book in}) to get the total hours worked.

Is there a way to also calculate over hours. If working past 6pm then anything after 6 would calculate as over hours?

2 Replies 2

Hi @Kim_Trager1 - this should work for you:

Have another formula field “Over Hours” with the formula

IF(HOUR({Log Out}) > 18, HOUR({Log Out}) - 18, 0) + IF(MINUTE({Log Out}) > 0, MINUTE({Log Out})/60, 0)

So, this has two parts - the number of hours greater than 18:00, plus the fraction of an hour, if any (for example if the person worked to 19:30). This gives the result as “3.5”, for e.g., so 3 and a half hours, not 3 hours 50 mins.

Screenshot 2019-03-25 at 22.24.43.png

Hope this helps! :grinning_face_with_smiling_eyes:


Works beautifully - Thank you.