A way to calculate over hours

[Kim_Trager1]

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?

[JonathanBowen]

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.

Hope this helps! :grinning_face_with_smiling_eyes:

JB

[Kim_Trager1]

JonathanBowen:

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

Works beautifully - Thank you.