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.

	Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min	Hrs	Min
Saturday	11	30	06	00	07	00	12	00	12	30	17	00	18	00	11	30
Sunday	09	30	20	00	21	00	02	00	02	30	05	00	06	00	09	30

<tbody>
</tbody>

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