Getting no of hours from start to finish

[Peter Mount]

Hello

I'm working on a time sheet.

I can get the day shift calculations working OK but I'm having trouble
with the night shift.

The formula I'm using is:

=IF(I19="Day";IF(SUM(K20:K21)>0;SUM(K21-K20)-TIME(0;30;0));IF(SUM(K20:K21)>0;SUM(K20-K21)-TIME(0;30;0)))

The night shift part is in the second half of the formula ie:

......IF(SUM(K20:K21)>0;SUM(K20-K21)-TIME(0;30;0)))

Times are in 24 hour format. "-TIME(0;30;0)" is to allow for a 30
minute lunch break

K20 holds the start time and K21 holds the finish time

The problem is when I choose 17:00 for the start time and 06:00 for the
finish time I get 10:30 hours worked iinsted of 12:30 hours worked. I
get the feeling it's subtracting 6AM from 5PM to tell me it's 10:30AM

How can I fix this so I get the number of hours from 17:00 (5PM) to
06:00 (6AM).

Thanks

Peter Mount

 

[Roger Govier]

Hi Peter

Try
=MOD(K21-K20,1)-TIME(0,30,0)

 

[Peter Mount]

Hi Roger

I modified your suggestion to be:

=IF(SUM(K20:K21)>0;MOD(K21-K20;1)-TIME(0;30;0);"")

That way if no times are entered for that day the total cell is blank
instead of ########, which I found to have an affect on the total for
the week at the end of the row of days.

Thanks very much for your help

Have fun

Peter Mount

 

[Roger Govier]

Hi Peter

I had not meant that formula was complete, merely that you should
replace that part of your formula.
Glad you got it sorted out.