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

 

[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

576:00 with that format? Regards

<tbody>
</tbody>

 

[mole999]

I got

576:00 with that format? Regards

<tbody>
</tbody>

which format

31/10/2015 16:58

01/11/2015 16:58

24:00

<colgroup><col span="2" width="124"><col width="72"></colgroup><tbody>
</tbody>

 

[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