Calculating Shift Length - overnight using 24 hour clock

[FayChurchward]

I need some help with a time sheet. I have set up a simple sheet, with the time people start and end their shift. I take one away from the other (=E3 -D3) to calculate how many hours they have worked and changed the cell format to custom [h]:mm However, the night shift, which is 22:00 to 08:00 is causing me problems, as it won't give me the correct shift length of 10 hours. All help appreciated! (I have highlighted in red the 'problem' cell. The other colours are from the sheet and hold no significance for this calculation)

	C	D	E	F	G
2	Monday	Start Time	End Time	Total Shift	Monday label
3	Day Off			0:00
4	0700-1600	07:00	16:00	9:00	AM
5	1600-2100	16:00	21:00	5:00	MAT
6	1515-2200	15:15	22:00	6:45	PM
7	Day Off			0:00
8	22:00-08:00	22:00	08:00	14:00	Night

<tbody>
</tbody>

 

[Snakehips]

FayChurchward,

Maybe....

Excel 2007
	C	D	E	F	G
2	Monday	Start Time	End Time	Total Shift	Monday label
3	Day Off			00:00
4	0700-1600	07:00	16:00	09:00	AM
5	1600-2100	16:00	21:00	05:00	MAT
6	1515-2200	15:15	22:00	06:45	PM
7	Day Off			00:00
8	22:00-08:00	22:00	08:00	10:00	Night
Sheet12

Cell Formulas
Range	Formula
F3	=IF(D3>E3,1-D3+E3,E3-D3 )

Hope that helps.

 

[FayChurchward]

Well it did, until I tried the same formula (with the corresponding column headings changed of course) in a subsequent column. The formula is =IF(E3>F3,1-E3+F3,F3-E3 ) The formatting for Start and End time is custom hh:mm for Total Shift is custom [h]:mm No idea why it won't work in Tuesday's column.

	Monday	Tuesday
Employee Number	Start Time	End Time	Total Shift	Start Time	End Time	Total Shift
c2004041			0:00	15:15	22:00	6:45
c2004042	07:00	16:00	9:00	15:15	22:00	6:45
c2004043	16:00	21:00	5:00	16:00	21:00	5:00
c2004044	15:15	22:00	6:45			0:00
c2004045			0:00	15:15	22:00	6:45
c2004046	22:00	08:00	10:00			0:00
c2004047			0:00			0:00
c2004048			0:00			0:00
c2004049	15:15	22:00	6:45			0:00
c2004050	15:15	22:00	6:45	07:00	16:00	9:00
c2004051			0:00	07:00	09:00	2:00
c2004052			0:00	16:00	22:00	6:00
c2004053			0:00			0:00
c2004054	07:00	16:00	9:00	15:15	22:00	6:45
c2004055	07:00	16:00	9:00	07:00	16:00	9:00
c2004056	22:00	08:00	10:00	22:00	08:00	###############
c2004057			0:00	22:00	08:00	###############
c2004058	07:00	16:00	9:00	15:15	22:00	6:45

<tbody>
</tbody>

 

[joeu2004]

I take one away from the other (=E3 -D3) to calculate how many hours they have worked and changed the cell format to custom [h]:mm However, the night shift, which is 22:00 to 08:00 is causing me problems, as it won't give me the correct shift length of 10 hours.

=MOD(E3-D3,1)

or

=E3-D3+(D3>E3)

formatted as Custom h:mm (or [h]:mm, if you wish).

 

[Snakehips]

Fay,
Check your formatting for cells and the formula you have in rows 18 and 19.

All the suggested formulas work fine with your posted data.