time values in if formulas excel 2007

[D sweetman]

Hi
See below a start and finish time of a shift. For John Doe I want the hours worked to read 24:00. See formulas used in the 2 hours worked for John and Peter.

Start		Finish	Name		Hours worked
14:00	Sleep over	14:00	John Doe		00:00
16:00	D/D	21:00	Peter Doe		05:00

<tbody>
</tbody>

=IF(D3="";"";(IF((D3>B3);(D3-B3);(24-(B3-D3)))))

<tbody>
</tbody>

=IF(D4="";"";(IF((D4>B4);(D4-B4);(24-(B4-D4)))))

<tbody>
</tbody>

Thanks for any help
Regards
David

 

[mole999]

576 is a result of the formula (which is the wrong way round)

in 2007 Pizza > Excel Options > Advanced > Editing Options

 

[mole999]

maybe replace
=IF(D4="";"";(IF((D4>B4);(D4-B4);(24-(B4-D4)))))
with
=ABS(D4-B4)

 

[joeu2004]

See below a start and finish time of a shift. For John Doe I want the hours worked to read 24:00.
[....]

Start		Finish	Name		Hours worked
14:00	Sleep over	14:00	John Doe		00:00

<tbody>
</tbody>

[....]

=IF(D3="";"";(IF((D3>B3);(D3-B3);(24-(B3-D3)))))

<tbody>
</tbody>

Change the formula to:

=IF(D3=""; ""; IF(D3 > B3; D3-B3; 1-(B3-D3)))

formatted as Custom [h]:mm.

Time is stored as a fraction of a day. So 1, not 24, represents 24 hours (one day).

PS: This is a good reason for using your formula instead of MOD(D3-B3,1), which is commonly suggested. Thanks for that.

 

[D sweetman]

Thanks for your patience
I must be missing something.
I cant make the 576 show as 12:00 with my formula.
I'm guessing the problem is with my formula
This is my result at the moment with the formulas shown below
The times are in columns b and d
and the times are formatted as [H]:mm as advised

Start		Finish	Name		Hours worked
14:00	Sleep over	14:00	John Doe		576:00

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

16:00

D/D

21:00

Peter Doe

5:00

<tbody>
</tbody>

22:00	D/D	22:00	Joe Doe		576:00

<tbody>
</tbody>

=IF(D3="";"";(IF((D3>B3);(D3-B3);(24-(B3-D3)))))

<tbody>
</tbody>

This formula is giving 576 in the first line but I want it to show 24:00

=IF(D4="";"";(IF((D4>B4);(D4-B4);(24-(B4-D4)))))

<tbody>
</tbody>

This formula is giving the correct answer of 5:00

=IF(D5="";"";(IF((D5>B5);(D5-B5);(24-(B5-D5)))))

<tbody>
</tbody>

Thanks

 

[mole999]

=IF(D3="";"";(IF((D3>B3);(D3-B3);(24-(B3-D3)))))

<tbody>
</tbody>

This formula is giving 576 in the first line but I want it to show 24:00

=IF(D4="";"";(IF((D4>B4);(D4-B4);(24-(B4-D4)))))

<tbody>
</tbody>

This formula is giving the correct answer of 5:00

=IF(D5="";"";(IF((D5>B5);(D5-B5);(24-(B5-D5)))))

<tbody>
</tbody>

Thanks

As Joeu2004 said 24 is 24 days (500 odd hours) change it to 1

 

[D sweetman]

Thanks a million Joeu2004,
That's what I was looking for.
Kind regards
David

 

[D sweetman]

Thanks Mole999
I appreciate the help its perfect now
Best regards
David

 

[D sweetman]

Hi again,
One last problem, I just noticed one person worked 24:30 see below but of course the hours worked comes up as 0:30.
I would like the formula to maybe know if the answer is less than 2 hours 24:00 should be added.
Or if cell c shows s/o which stands for sleep over and the hours are less than say 10 it will add 24:00 to the result.
I hope I'm explaining this right.

Start

Shift

Finish

Name

Hours worked

<tbody>
</tbody>

14:00

s/o

14:30

John Doe

0:30

<tbody>
</tbody>

Regards
David

 

[mole999]

you'll never have that problem if you ensure you have a start / finish date

 

[D sweetman]

Thanks Mole999
Yeah maybe I can include that as a solution.
Regards
David