New Excel Forum

This forum has been moved to TeachExcel.

Ask all future questions in the new excel forum.


How to calculate hours worked minus break

Formulas, Functions, Formatting, Charts, Data Analysis, etc.
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at

How to calculate hours worked minus break

Postby fedcco » Wed Dec 28, 2011 11:36 am

I'm a novice to excel and need help creating a formula that will calculate hours worked less a lunch break of 30 min. if working more than 5 hours for all 3 shifts. Ex. A1 contains time in. B1 contains time out and C1 the result. If Joe worked 4 hours from 7a-11a no lunch taken. If he works 7a-3:30p 30 min. unpaid break is deducted. Same applies if he works from 11p-3a, no break is deducted but if he works 11p-7:30a a 30 min. unpaid break is deducted from his time.

=IF(B3<A3,B3+1,B3)-A3 This formula will work for all shifts. I just need the balance to deduct the breaks.

Time In Time Out Hours Wk’d
11:00 PM 8:00 AM 9
  • 0

Posts: 1
Joined: Dec 28, 2011
Reputation: 0

Re: How to calculate hours worked minus break

Postby Sisyphus » Wed Dec 28, 2011 9:41 pm

The formula you seek will consist of a couple of nested IF functions, but before you can get your teeth into it you will need to establish the rules far more precisely than you have published. Start by fixing the lunch break, perhaps at the top of your sheet, say,
From 11:30 To 13:30.

Now your formula should express the following: Deduct 30 minutes (but not more than the total) from the time the employee was present during the lunch break.
Now, that should be a lot easier if your lunch break wouldn't last for more than 30 minutes, a fact I do not know.

I think you should also take the facts on the ground into consideration. For example, some one punches in 10 minutes before the end of the lunch break. By the above formula he wouldn't get paid for those 10 minutes even though he didn't use them to have lunch. As a result no one likes to show up before "paid" time. But such thoughts wouldn't be necessary if the factory would shut down during the lunch break so that no one can work anyway, and there are other rules in place that prevent people from arriving late because they don't want to be early.

So, the hard part isn't the formula. It's the rules. :D
  • 0

Have a great day! :D

I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Former Moderator
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: How to calculate hours worked minus break

Postby Shafiullah » Mon Jan 02, 2012 12:44 pm

I am sending you a formula for networking hours, I hope it will help you.
  • 0

You do not have the required permissions to view the files attached to this post.
Posts: 55
Joined: Dec 6, 2011
Reputation: 0

Re: How to calculate hours worked minus break

Postby joaofigu » Thu Mar 16, 2017 5:42 am

HI there, thanks,
I ´m looking for a formula like networking hours, with diffenrte days, i want to now how much time it takes to a team that work since 9h to 18h With a lunch hour(12h13h) takes to do a job in more than a day... start for example 1/3/2017 10h and ends a 3/3/2017 at 14H .

Thanks sorry about my english :)
  • 0

Posts: 1
Joined: Mar 16, 2017
Reputation: 0
Excel Version: 2013

Return to General Excel Questions

Who is online

Users browsing this forum: Baidu [Spider], Google [Bot] and 5 guests