Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 25, 2019 09:47 AM
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?
Mar 25, 2019 03:26 PM
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
Mar 26, 2019 04:50 AM
Works beautifully - Thank you.