# delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

1. ## delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

i'm sure this has been asked more than once before but i've spent hours wading thru thousands of posts and search hasn't been any more helpful so thanks for understanding

what i need is a formula that will take the sum of start and end times then check if it is greater than 6 hours and if true will deduct a half hour from hours worked.
i also need it to display 0 hours worked if anything other than a start time is entered, like off, or paid day off etc

2. ## Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

Try

=IFERROR((B2-A2)-((B2-A2>="6:00"+0)*"0:30"),0)

A2 = Start Time
B2 = End Time

3. ## Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

thanks for the help!

this works beautifully

4. ## Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

You're welcome.

AFter some thought, may need to adjust if you have TimeFrames that span midnight.
Like if Start was 20:00 and End was 4:00

=IF(COUNT(A2:B2)=2,MOD(B2-A2,1)-((MOD(B2-A2,1)>="6:00"+0)*"0:30"),0)

5. ## Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

This is my first post so I hope I am doing this right lol!

I am having a simular issue with deducting lunch breaks from daily in/out times. Due to business some days we take 1/2 hour lunches or 1 hour lunches when 5 or more hours worked. If an employee works less than 5 hours then no lunch is given and the time should not be deducted.

I attached a sample of the spreadsheet I am working on with the two formulas, and I am trying to figure out but with no luck. I have added comments to help explain my situation with both formulas.

Thank you

6. ## Re: delete half hour for lunch if time >6 hrs and 0 if blank or off entered for start time

Hi Wayoutman,

I have however restructured your example

so please next time keep to the forum rules

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