Calculating time of shifts including past midnight shifts.

[Diesel9a1]

Not quite as simple as the title says. I'm trying to get the timesheet to add up the hours worked. There are several on off's throughout the day / night shifts, each time entry comprises of an hours cell and a minutes cell.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Hrs[/TD]
[TD]Min[/TD]
[TD]Hrs[/TD]
[TD]Min[/TD]
[TD]Hrs[/TD]
[TD]Min[/TD]
[TD]Hrs[/TD]
[TD]Min[/TD]
[TD]Hrs[/TD]
[TD]Min[/TD]
[TD]Hrs[/TD]
[TD]Min[/TD]
[TD]Hrs[/TD]
[TD]Min[/TD]
[TD]Hrs[/TD]
[TD]Min[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]11[/TD]
[TD]30[/TD]
[TD]06[/TD]
[TD]00[/TD]
[TD]07[/TD]
[TD]00[/TD]
[TD]12[/TD]
[TD]00[/TD]
[TD]12[/TD]
[TD]30[/TD]
[TD]17[/TD]
[TD]00[/TD]
[TD]18[/TD]
[TD]00[/TD]
[TD]11[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]09[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]00[/TD]
[TD]21[/TD]
[TD]00[/TD]
[TD]02[/TD]
[TD]00[/TD]
[TD]02[/TD]
[TD]30[/TD]
[TD]05[/TD]
[TD]00[/TD]
[TD]06[/TD]
[TD]00[/TD]
[TD]09[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

I'm unable to merge cells in this post so please bear with me.
The first Hrs/Min are the rostered hours.
Second Hrs/ Min are Depart Depot
Third Hrs / Min are Arrive on site
Fourth Hrs / Min are Stop for dinner
Fifth Hrs / Min are Start work after dinner
Sixth Hrs / Min are Depart Site for depot
Seventh Hrs / Min are Arrive at depot
Eighth and final Hrs / Min are Hours worked.

First and final Hrs / Mins should equal each other.

I'm trying to get the final Hrs / Mins to add up all the hours actually worked (including the travel to and from site).

The Hrs / Mins columns need to be separate to help the non computer literate from not putting in the : etc.

Thanks in advance

Pete

 

[FormR]

Hi, here is one possible option you could try:

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2		Rostered		Depart		Arrive		Dinner		Start		Depart		Arrive		Final
3		Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min
4	Saturday	11	30	6	0	7	0	12	0	12	30	17	0	18	0	11	30
5	Sunday	9	30	20	0	21	0	2	0	2	30	5	0	6	0	9	30
Sheet1

Cell Formulas
Range	Formula
P4	=INT((MOD(TIME(N4,O4,0)-TIME(D4,E4,0),1)-MOD(TIME(J4,K4,0)-TIME(H4,I4,0),1))*24)
Q4	=MINUTE(MOD(TIME(N4,O4,0)-TIME(D4,E4,0),1)-MOD(TIME(J4,K4,0)-TIME(H4,I4,0),1))