How to calculate hours worked minus break

[fedcco]

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

[Sisyphus]

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

[Shafiullah]

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

[joaofigu]

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