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>

Start	Finnish	Hours	Start	Finnish	Hours	Start	Finnish	Hours	Total
06:00	14:00	07:30	06:00	16:00	09:30	06:00	14:00	07:30	24:30
06:00	14:00	07:30	06:00	16:00	09:30	06:00	14:00	07:30	24:30
12:00	00:00	11:30	12:00	00:00	11:30	12:00	01:00	12:30	1691:30
12:00	00:00	11:30	12:00	00:00	11:30	12:00	01:00	12:30	1691:30

<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>
</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")