# Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours

1. ## Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours 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  Register To Reply

2. ## Re: Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours

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  Register To Reply

3. ## Re: Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours

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...  Register To Reply

4. ## Re: Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours

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, to ``Please Login or Register  to view this content.``
Change 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 enter ``Please Login or Register  to view this content.``

Regards  Register To Reply

5. ## Re: Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours

Hi

Try: =IF(MOD(C5-B5, 1)>=4.5/24,MOD(C5-B5, 1)-0.5/24,MOD(C5-B5, 1))  Register To Reply