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

 

[D sweetman]

Sorry I meant to include that start is in column b and finish is column d

 

[Trevor_S]

Do you mind the hours worked being decimal (rather than hours:mins)? If that's ok, just reformat the column as a number rather than as a time.
(Also the semicolons in the formula should be commas)

 

[D sweetman]

Thanks
Yes ideally HH/MM and I tried commas but I got an error and no error when I used semi colons. Strange!!!

 

[D sweetman]

Also if I use decimal the first one works but the second one is then wrong.

 

[mole999]

try custom format [h]:mm that will display as 24:00

 

[D sweetman]

I got [TABLE="width: 175"]
<tbody>[TR]
[TD]576:00
with that format?
Regards[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

I got [TABLE="width: 175"]
<tbody>[TR]
[TD]576:00
with that format?
Regards[/TD]
[/TR]
</tbody>[/TABLE]

which format

[TABLE="width: 320"]
<colgroup><col span="2" width="124"><col width="72"></colgroup><tbody>[TR]
[TD="class: xl63, width: 124, align: right"]31/10/2015 16:58[/TD]
[TD="class: xl63, width: 124, align: right"]01/11/2015 16:58[/TD]
[TD="class: xl64, width: 72, align: right"]24:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

Thanks
Yes ideally HH/MM and I tried commas but I got an error and no error when I used semi colons. Strange!!!

delimiters, are a reflection of your system settings

 

[D sweetman]

Hi
I got 576 instead of 24:00 and I formatted the cell [H]:mm
Also how do I change the delimiter back to a comma, it used to be a comma.
Regards
David