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

Hi Peter

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

--
Regards

Roger Govier


"Peter Mount" wrote in message
ps.com...
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

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 wrote:
Hi Peter

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

--
Regards

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.

--
Regards

Roger Govier


"Peter Mount" wrote in message
ups.com...
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 wrote:
Hi Peter

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

--
Regards

Roger Govier