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.

[TABLE="width: 426"]
<tbody>[TR]
[TD]Start[/TD]
[TD][/TD]
[TD]Finish[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]Hours worked[/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD] Sleep over[/TD]
[TD]14:00[/TD]
[TD]John Doe[/TD]
[TD][/TD]
[TD]00:00[/TD]
[/TR]
[TR]
[TD]16:00[/TD]
[TD]D/D[/TD]
[TD]21:00[/TD]
[TD]Peter Doe[/TD]
[TD][/TD]
[TD]05:00[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 350"]
<tbody>[TR]
[TD]=IF(D3="";"";(IF((D3>B3);(D3-B3);(24-(B3-D3)))))

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 350"]
<tbody>[TR]
[TD="class: xl65, width: 350"]=IF(D4="";"";(IF((D4>B4);(D4-B4);(24-(B4-D4)))))[/TD]
[/TR]
</tbody>[/TABLE]

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.
[....]
[TABLE="class: grid, width: 426"]
<tbody>[TR]
[TD]Start[/TD]
[TD][/TD]
[TD]Finish[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]Hours worked[/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD] Sleep over[/TD]
[TD]14:00[/TD]
[TD]John Doe[/TD]
[TD][/TD]
[TD]00:00[/TD]
[/TR]
</tbody>[/TABLE]
[....]
[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD]=IF(D3="";"";(IF((D3>B3);(D3-B3);(24-(B3-D3)))))[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 477"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Start[/TD]
[TD][/TD]
[TD]Finish[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]Hours worked[/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD]Sleep over[/TD]
[TD]14:00[/TD]
[TD]John Doe[/TD]
[TD][/TD]
[TD]576:00
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 477"]
<tbody>[TR]
[TD="class: xl65, width: 40"]16:00[/TD]
[TD="class: xl66, width: 57"]D/D[/TD]
[TD="class: xl65, width: 101"]21:00[/TD]
[TD="width: 69"]Peter Doe[/TD]
[TD="width: 35"][/TD]
[TD="class: xl67, width: 175"]5:00
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 477"]
<tbody>[TR]
[TD="class: xl65, width: 40"]22:00[/TD]
[TD="class: xl66, width: 57"]D/D[/TD]
[TD="class: xl65, width: 101"]22:00[/TD]
[TD="width: 69"]Joe Doe[/TD]
[TD="width: 35"][/TD]
[TD="class: xl67, width: 175"]576:00[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 40"][/TD]
[TD="class: xl66, width: 57"][/TD]
[TD="class: xl65, width: 101"][/TD]
[TD="width: 69"][/TD]
[TD="width: 35"][/TD]
[TD="class: xl67, width: 175"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 350"]
<tbody>[TR]
[TD]=IF(D3="";"";(IF((D3>B3);(D3-B3);(24-(B3-D3)))))[/TD]
[/TR]
</tbody>[/TABLE]
This formula is giving 576 in the first line but I want it to show 24:00[TABLE="width: 350"]
<tbody>[TR]
[TD="class: xl65, width: 350"]=IF(D4="";"";(IF((D4>B4);(D4-B4);(24-(B4-D4)))))[/TD]
[/TR]
</tbody>[/TABLE]
This formula is giving the correct answer of 5:00
[TABLE="width: 350"]
<tbody>[TR]
[TD="class: xl65, width: 350"]=IF(D5="";"";(IF((D5>B5);(D5-B5);(24-(B5-D5)))))[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[mole999]

[TABLE="width: 350"]
<tbody>[TR]
[TD]=IF(D3="";"";(IF((D3>B3);(D3-B3);(24-(B3-D3)))))
[/TD]
[/TR]
</tbody>[/TABLE]
This formula is giving 576 in the first line but I want it to show 24:00[TABLE="width: 350"]
<tbody>[TR]
[TD="class: xl65, width: 350"]=IF(D4="";"";(IF((D4>B4);(D4-B4);(24-(B4-D4)))))
[/TD]
[/TR]
</tbody>[/TABLE]
This formula is giving the correct answer of 5:00
[TABLE="width: 350"]
<tbody>[TR]
[TD="class: xl65, width: 350"]=IF(D5="";"";(IF((D5>B5);(D5-B5);(24-(B5-D5)))))
[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 477"]
<tbody>[TR]
[TD="class: xl65, width: 40"]Start[/TD]
[TD="class: xl65, width: 57"]Shift[/TD]
[TD="class: xl65, width: 101"]Finish[/TD]
[TD="class: xl65, width: 69"]Name[/TD]
[TD="class: xl65, width: 35"][/TD]
[TD="class: xl65, width: 175"]Hours worked[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 493"]
<tbody>[TR]
[TD]14:00[/TD]
[TD]s/o[/TD]
[TD]14:30[/TD]
[TD]John Doe[/TD]
[TD][/TD]
[TD]0:30[/TD]
[/TR]
</tbody>[/TABLE]

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