time calculations

[jtodd]

Hi
I have a sheet that works out hours worked which all works fine till I hit exactly midnight .( see extract)
I am using this formula =(IF(F18>E18,F18-E18,24-E18+F18))-"00:30" (half an hour lunch)
I am using [h]:mm to format the totals but as you can see it does not work for the bottom 2

<colgroup><col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> </colgroup><tbody>
</tbody>

<colgroup><col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:2084; width:43pt" span="2" width="57"> <col style="width:48pt" span="5" width="64"> </colgroup><tbody>
[TD="class: xl66, width: 64"]Start
[/TD]
[TD="class: xl66, width: 64"]Finnish
[/TD]
[TD="class: xl66, width: 64"]Hours[/TD]
[TD="class: xl66, width: 57"]Start [/TD]
[TD="class: xl66, width: 57"]Finnish [/TD]
[TD="class: xl66, width: 64"]Hours[/TD]
[TD="class: xl66, width: 64"]Start [/TD]
[TD="class: xl66, width: 64"]Finnish [/TD]
[TD="class: xl66, width: 64"]Hours[/TD]
[TD="class: xl66, width: 64"]Total [/TD]

[TD="class: xl68"]06:00[/TD]
[TD="class: xl68"]14:00
[/TD]
[TD="class: xl65"]07:30[/TD]
[TD="class: xl68"]06:00[/TD]
[TD="class: xl68"]16:00[/TD]
[TD="class: xl65"]09:30[/TD]
[TD="class: xl68"]06:00[/TD]
[TD="class: xl68"]14:00[/TD]
[TD="class: xl65"]07:30[/TD]
[TD="class: xl67"]24:30
[/TD]

[TD="class: xl68"]06:00[/TD]
[TD="class: xl68"]14:00[/TD]
[TD="class: xl65"]07:30[/TD]
[TD="class: xl68"]06:00[/TD]
[TD="class: xl68"]16:00[/TD]
[TD="class: xl65"]09:30[/TD]
[TD="class: xl68"]06:00[/TD]
[TD="class: xl68"]14:00[/TD]
[TD="class: xl65"]07:30[/TD]
[TD="class: xl67"]24:30
[/TD]

[TD="class: xl68"]12:00[/TD]
[TD="class: xl68"]00:00[/TD]
[TD="class: xl65"]11:30[/TD]
[TD="class: xl68"]12:00[/TD]
[TD="class: xl68"]00:00[/TD]
[TD="class: xl65"]11:30[/TD]
[TD="class: xl68"]12:00[/TD]
[TD="class: xl68"]01:00[/TD]
[TD="class: xl65"]12:30[/TD]
[TD="class: xl67"]1691:30[/TD]

[TD="class: xl68"]12:00[/TD]
[TD="class: xl68"]00:00[/TD]
[TD="class: xl65"]11:30[/TD]
[TD="class: xl68"]12:00[/TD]
[TD="class: xl68"]00:00[/TD]
[TD="class: xl65"]11:30[/TD]
[TD="class: xl68"]12:00[/TD]
[TD="class: xl68"]01:00[/TD]
[TD="class: xl65"]12:30[/TD]
[TD="class: xl67"]1691:30
[/TD]

</tbody>

Presume it is something to do with the formatting of the Total , unless there is a better approach ?

 

[mrhstn]

Time is a fickle thing in Excel as it's a number masked to look like time. Could very well be you've copied time from a previous entry and it's 70 days in the past...

Use the MOD function to get round this

Book1
	A	B	C	D	E	F	G	H	I	J
1	Start	Finnish	Hours	Start	Finnish	Hours	Start	Finnish	Hours	Total
2	06:00	14:00	07:30	06:00	16:00	09:30	06:00	14:00	07:30	24:30:00
3	06:00	14:00	07:30	06:00	16:00	09:30	06:00	14:00	07:30	24:30:00
4	12:00	00:00	11:30	12:00	00:00	11:30	12:00	01:00	12:30	35:30:00
5	12:00	00:00	11:30	12:00	00:00	11:30	12:00	01:00	12:30	35:30:00
Sheet1

Cell Formulas
Range	Formula
I2	=MOD(H2-G2, 1)-TIMEVALUE("00:30:00")
J2	=I2+F2+C2
C2	=MOD(B2-A2, 1)-TIMEVALUE("00:30:00")
F2	=MOD(E2-D2, 1)-TIMEVALUE("00:30:00")