Please help! I am trying to create a formula that calculates hours worked, but deduct a 30 minute lunchbreak only if the employee worked a total of 4.5 hours or more in that day. See attached spreadsheet
Please help! I am trying to create a formula that calculates hours worked, but deduct a 30 minute lunchbreak only if the employee worked a total of 4.5 hours or more in that day. See attached spreadsheet
There is likely a shorter/easier way to do this, but you can try this:
=IF(MOD(C5-B5, 1*24)>4.5/24,MOD(C5-B5, 1*24)-1/48,MOD(C5-B5, 1*24))
- Moo
Thank you! the only problem is when I enter time of 10p-12am (hours less than 4.5 hrs in a night shift) it still deducts 30 minutes...
Hello Nikkiw
Why don't you enter the time in the 24 hr format, i.e. 10:00 or 14:15, etc.
I suggest to use a simple subtraction like C5-B5, or if you need the 4.5 hr rule, toChange the format of the total hours to time format so it looks visually correct. If you want to the total in a calculation, say to determine the pay for the person, you can then you can enterPlease Login or Register to view this content.
whatever your rate isPlease Login or Register to view this content.
Regards
Hi
Try: =IF(MOD(C5-B5, 1)>=4.5/24,MOD(C5-B5, 1)-0.5/24,MOD(C5-B5, 1))
Regards Kevin
Merged Cells (They are the work of the devil!!!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks