Factoring breaks into Excel time calculations

Hello All:

I'm trying to calculate total hours worked for each of my staff members but am having difficulties with the IF statement.

Basically what i've done thus far is this:

B1-A1=C1

Out Time - In Time = Total Hours Worked

16:00-08:00=8:00

However, I need to deduct 30 min for break if they work over 5 hours and if they don't they aren't to be deducted any time.

Please Help!

re: Factoring breaks into Excel time calculations

Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

re: Factoring breaks into Excel time calculations

Given two time columns, D (start) and E (end) and the break in decimal, F:
=((E13-D13)*24)-F13

re: Factoring breaks into Excel time calculations

Thanks for that, however that only calculates a break for everyone.

I need an if statement to calculate a 30 min break only if they work more then 5 hours.

re: Factoring breaks into Excel time calculations

Heya, Gkarasiewicz.

I'm thinking use the IF function. Something like
The syntax might be off a bit, but from what I remember of Excel, that should be about right.

re: Factoring breaks into Excel time calculations

I got it to work with this =IF(E3>5.5,E3-0.5,E3)

I had created 4 colums C3(start time) D3(End Time) E3(End Time - Start Time) F (=IF(E3>5.5,E3-0.5,E3))

Thanks All For The Help

re: Factoring breaks into Excel time calculations

Heya, GKarasiewicz.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)

re: Factoring breaks into Excel time calculations

I've just just stumbled across this and it's almost exactly what I am having a problem with.

I am using the same formula to deduct a 30 min break from any shift that is over 6 hours but it seems that as I have used the hh:mm cell format I can't subtract the break.

, E4 being the total hours worked but in hh:mm format.

If I try it with decimal value's it works fine but could anyone help as to how to get this to work with a time value?

I tested it and its definitely the E4>6 part that isn't working, if I change the false value to another cell it displays that everytime.

Cheers in advance!

Gareth.