Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Subtract start time from end time minus 30 minutes lunch break

1. Member
Join Date
19th September 2012
Posts
73

Subtract start time from end time minus 30 minutes lunch break

I have an Excel spreadsheet with start time in column A and end time in column B. The formula I came up with displayed the time difference as “Negative dates or times are displayed as ##########. What this means is that the formula is not able to handle times past 12 midnight. I would like the formula to take into consideration time difference past midnight in order to fix the problem.

Here is my formula:

=IF(OR(ISBLANK(B3),ISBLANK(A3)),"",B3-A3-(B3-A3>TIME(8,0,0))*TIME(0,30,0))

NOTE: The formula works fine for deducting the 30 minutes lunch break if someone works an eight (8) hour shift (for example, 8:00am to 4:30pm; where the 30 minutes is lunch break. So instead of 8:30 minutes, the formula works the way it should (i.e. show 8:00 hours). It also works fine for time difference such as 8:00pm minus 4:00pm = 4: 00. However, if the time difference is 8:30 pm minus 4:00 pm, the 30 minutes is part of the solution instead of displaying 4 hours. I want the formula for the time difference to fix this as well.
I have attached a sample copy of the data.

Excel Video Tutorials / Excel Dashboards Reports

2. Senior Member
Join Date
18th September 2010
Posts
127

Re: Subtract start time from end time minus 30 minutes lunch break

Try:
=B3-A3+(A3>B3)-"0:30"
Look here:
http://www.myonlinetraininghub.com/e...ulation-tricks

Excel Video Tutorials / Excel Dashboards Reports

3. Member
Join Date
19th September 2012
Posts
73

Re: Subtract start time from end time minus 30 minutes lunch break

Armando: the formula suggested did work but it displayed 3:30 instead of 4:00 hours for 3pm minus 11am instead of 4 hours. What I want is for the formula to subtract times such as 3pm minus 11am, 4pm minus 11am and so on. However, I want the formula to subtract 30 minutes from the time difference if it is 2:30pm minus 1am.

Excel Video Tutorials / Excel Dashboards Reports

4. Senior Member
Join Date
18th September 2010
Posts
127

Re: Subtract start time from end time minus 30 minutes lunch break

Try:
=IF((B3<A3)+B3-A3>8/24,(B3<A3)+B3-A3-0.5/24,(B3<A3)+B3-A3)
Or:
=IF(OR(A3="",B3=""),"",IF((B3<A3)+B3-A3>8/24,(B3<A3)+B3-A3-0.5/24,(B3<A3)+B3-A3))

Excel Video Tutorials / Excel Dashboards Reports

5. Re: Subtract start time from end time minus 30 minutes lunch break

Hi

Those hours in Col C are the y basic hours! Not overtime!

Try this, it will calculate the hours after 8.5, also past midnight: =IF(COUNTA(A3:B3)<2,"",MAX(MOD(B3-A3,1)-(1/24*8.5),0))

Excel Video Tutorials / Excel Dashboards Reports

6. Member
Join Date
19th September 2012
Posts
73

Re: Subtract start time from end time minus 30 minutes lunch break

Armando: Thank you so much for the assistance. The suggested formulas did the trick.

Excel Video Tutorials / Excel Dashboards Reports

7. Re: Subtract start time from end time minus 30 minutes lunch break

Glad you got it sorted, so all your staff are on overtime as soon as they turn up, perhaps I should apply for a job!!

Excel Video Tutorials / Excel Dashboards Reports

8. Senior Member
Join Date
18th September 2010
Posts
127

Re: Subtract start time from end time minus 30 minutes lunch break

Glad it worked and thanks for the feedback.

Excel Video Tutorials / Excel Dashboards Reports

Users Browsing this Thread

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
•
porno