# 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

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

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...

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

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))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1