Split time in to shifts

[nitroxed]

Hi

I have spent SO much time in Excel trying to get this to work but have failed miserably, when I think I'm there, I test it and it fails....

This is the scenario:

We pay staff weekly based on 4 different categories of shift as follows (all paid at different hourly rates) which they submit a timesheet for:

Shift	Start	End
Mon-Fri Day	06:00	20:00
Mon-Fri Night	20:00	06:00
Saturday	00:00	23:59
Sunday	00:00	23:59

<tbody>
</tbody>

The complexity of trying to get a formula to calculate the number of hours in each category blows my mind ! For example, we might have an employee who started at 1900hrs on Friday, and finished at 0800hrs on Saturday morning. A total shift length of 13hrs. Splitting this as per the shift table above actually gives rise to the following hours and shifts:

Mon-Fri Day 1hr
Mon-Fri Night 4hrs
Saturday 8hrs

What I would like ideally is the ability to enter start times and end times in to a spreadsheet and based on this and the table above, split their time in to the respective shift categories.

Any help greatly appreciated !

Thanks

 

[oldbrewer]

We pay staff weekly based on 4 different categories of shift as follows (all paid at different hourly rates) which they submit a timesheet for:						01:00
Shift	Start	End	rate $ / hour	sun	01/01/2017 00:00	00:00	25	1	Sun	Sun00:00	0
Mon-Fri Day	06:00	20:00	12			01/01/2017 01:00	01:00	25	2	Sun	Sun01:00	25
Mon-Fri Night	20:00	06:00	14			01/01/2017 02:00	02:00	25	3	Sun	Sun02:00	50
Saturday	00:00	23:59	17			01/01/2017 03:00	03:00	25	4	Sun	Sun03:00	75
Sunday	00:00	23:59	25			01/01/2017 04:00	04:00	25	5	Sun	Sun04:00	100
						01/01/2017 05:00	05:00	25	6	Sun	Sun05:00	125
employee who started at 1900hrs on Friday, and finished at 0800hrs on Saturday morning.						01/01/2017 06:00	06:00	25	7	Sun	Sun06:00	150
			Sun			01/01/2017 07:00	07:00	25	8	Sun	Sun07:00	175
Mon-Fri Day 1hr						01/01/2017 08:00	08:00	25	9	Sun	Sun08:00	200
Mon-Fri Night 4hrs						01/01/2017 09:00	09:00	25	10	Sun	Sun09:00	225
Saturday 8hrs						01/01/2017 10:00	10:00	25	11	Sun	Sun10:00	250
						01/01/2017 11:00	11:00	25	12	Sun	Sun11:00	275
						01/01/2017 12:00	12:00	25	13	Sun	Sun12:00	300
						01/01/2017 13:00	13:00	25	14	Sun	Sun13:00	325
						01/01/2017 14:00	14:00	25	15	Sun	Sun14:00	350
						01/01/2017 15:00	15:00	25	16	Sun	Sun15:00	375
start	fri	19:00	fri19:00			01/01/2017 16:00	16:00	25	17	Sun	Sun16:00	400
finish	sat	08:00	sat08:00			01/01/2017 17:00	17:00	25	18	Sun	Sun17:00	425
pay				204	$	01/01/2017 18:00	18:00	25	19	Sun	Sun18:00	450
						01/01/2017 19:00	19:00	25	20	Sun	Sun19:00	475
						01/01/2017 20:00	20:00	25	21	Sun	Sun20:00	500
						01/01/2017 21:00	21:00	25	22	Sun	Sun21:00	525
						01/01/2017 22:00	22:00	25	23	Sun	Sun22:00	550
I made a table from sun to sun = 8 days						01/01/2017 23:00	23:00	25	24	Sun	Sun23:00	575
and put in the $ rate for each hour					mon	02/01/2017 00:00	00:00	25	25	Mon	Mon00:00	600
						02/01/2017 01:00	01:00	14	27	Mon	Mon01:00	614
these were cumulatively summed						02/01/2017 02:00	02:00	14	28	Mon	Mon02:00	628
and a subtraction gives the pay						02/01/2017 03:00	03:00	14	29	Mon	Mon03:00	642
						02/01/2017 04:00	04:00	14	30	Mon	Mon04:00	656
the 204 $ comes from						02/01/2017 05:00	05:00	14	31	Mon	Mon05:00	670
						02/01/2017 06:00	06:00	14	32	Mon	Mon06:00	684
=OFFSET($L$2,MATCH(D20,$L$3:$L$999,0),1)-OFFSET($L$2,MATCH(D19,$L$3:$L$999,0),1)				02/01/2017 07:00	07:00	12	33	Mon	Mon07:00	696
						02/01/2017 08:00	08:00	12	34	Mon	Mon08:00	708
the helper table can be hidden if desired						02/01/2017 09:00	09:00	12	35	Mon	Mon09:00	720
						02/01/2017 10:00	10:00	12	36	Mon	Mon10:00	732
						02/01/2017 11:00	11:00	12	37	Mon	Mon11:00	744
						02/01/2017 12:00	12:00	12	38	Mon	Mon12:00	756
						02/01/2017 13:00	13:00	12	39	Mon	Mon13:00	768
						02/01/2017 14:00	14:00	12	40	Mon	Mon14:00	780
						02/01/2017 15:00	15:00	12	41	Mon	Mon15:00	792
						02/01/2017 16:00	16:00	12	42	Mon	Mon16:00	804
						02/01/2017 17:00	17:00	12	43	Mon	Mon17:00	816
						02/01/2017 18:00	18:00	12	44	Mon	Mon18:00	828
						02/01/2017 19:00	19:00	12	45	Mon	Mon19:00	840
						02/01/2017 20:00	20:00	12	46	Mon	Mon20:00	852
						02/01/2017 21:00	21:00	14	47	Mon	Mon21:00	866
						02/01/2017 22:00	22:00	14	48	Mon	Mon22:00	880
						02/01/2017 23:00	23:00	14	49	Mon	Mon23:00	894
					tue	02/01/2017 23:59	00:00	14	50	Mon	Tue00:00	908
						03/01/2017 01:00	01:00	14	52	Tue	Tue01:00	922
						03/01/2017 02:00	02:00	14	53	Tue	Tue02:00	936
						03/01/2017 03:00	03:00	14	54	Tue	Tue03:00	950
						03/01/2017 04:00	04:00	14	55	Tue	Tue04:00	964
						03/01/2017 05:00	05:00	14	56	Tue	Tue05:00	978
						03/01/2017 06:00	06:00	14	57	Tue	Tue06:00	992
						03/01/2017 07:00	07:00	12	58	Tue	Tue07:00	1004
						03/01/2017 08:00	08:00	12	59	Tue	Tue08:00	1016
						03/01/2017 09:00	09:00	12	60	Tue	Tue09:00	1028
						03/01/2017 10:00	10:00	12	61	Tue	Tue10:00	1040
						03/01/2017 11:00	11:00	12	62	Tue	Tue11:00	1052
						03/01/2017 12:00	12:00	12	63	Tue	Tue12:00	1064
						03/01/2017 13:00	13:00	12	64	Tue	Tue13:00	1076
						03/01/2017 14:00	14:00	12	65	Tue	Tue14:00	1088
						03/01/2017 15:00	15:00	12	66	Tue	Tue15:00	1100
						03/01/2017 16:00	16:00	12	67	Tue	Tue16:00	1112
						03/01/2017 17:00	17:00	12	68	Tue	Tue17:00	1124
						03/01/2017 18:00	18:00	12	69	Tue	Tue18:00	1136
						03/01/2017 19:00	19:00	12	70	Tue	Tue19:00	1148
						03/01/2017 20:00	20:00	12	71	Tue	Tue20:00	1160
						03/01/2017 21:00	21:00	14	72	Tue	Tue21:00	1174
						03/01/2017 22:00	22:00	14	73	Tue	Tue22:00	1188
						03/01/2017 23:00	23:00	14	74	Tue	Tue23:00	1202
					wed	03/01/2017 23:59	00:00	14	75	Tue	Wed00:00	1216
						04/01/2017 01:00	01:00	14	77	Wed	Wed01:00	1230
						04/01/2017 02:00	02:00	14	78	Wed	Wed02:00	1244
						04/01/2017 03:00	03:00	14	79	Wed	Wed03:00	1258
						04/01/2017 04:00	04:00	14	80	Wed	Wed04:00	1272
						04/01/2017 05:00	05:00	14	81	Wed	Wed05:00	1286
						04/01/2017 06:00	06:00	14	82	Wed	Wed06:00	1300
						04/01/2017 07:00	07:00	12	83	Wed	Wed07:00	1312
						04/01/2017 08:00	08:00	12	84	Wed	Wed08:00	1324
						04/01/2017 09:00	09:00	12	85	Wed	Wed09:00	1336
						04/01/2017 10:00	10:00	12	86	Wed	Wed10:00	1348
						04/01/2017 11:00	11:00	12	87	Wed	Wed11:00	1360
						04/01/2017 12:00	12:00	12	88	Wed	Wed12:00	1372
						04/01/2017 13:00	13:00	12	89	Wed	Wed13:00	1384
						04/01/2017 14:00	14:00	12	90	Wed	Wed14:00	1396
						04/01/2017 15:00	15:00	12	91	Wed	Wed15:00	1408
						04/01/2017 16:00	16:00	12	92	Wed	Wed16:00	1420
						04/01/2017 17:00	17:00	12	93	Wed	Wed17:00	1432
						04/01/2017 18:00	18:00	12	94	Wed	Wed18:00	1444
						04/01/2017 19:00	19:00	12	95	Wed	Wed19:00	1456
						04/01/2017 20:00	20:00	12	96	Wed	Wed20:00	1468
						04/01/2017 21:00	21:00	14	97	Wed	Wed21:00	1482
						04/01/2017 22:00	22:00	14	98	Wed	Wed22:00	1496
						04/01/2017 23:00	23:00	14	99	Wed	Wed23:00	1510
					thu	04/01/2017 23:59	00:00	14	100	Wed	Thu00:00	1524
						05/01/2017 01:00	01:00	14	102	Thu	Thu01:00	1538
						05/01/2017 02:00	02:00	14	103	Thu	Thu02:00	1552
						05/01/2017 03:00	03:00	14	104	Thu	Thu03:00	1566
						05/01/2017 04:00	04:00	14	105	Thu	Thu04:00	1580
						05/01/2017 05:00	05:00	14	106	Thu	Thu05:00	1594
						05/01/2017 06:00	06:00	14	107	Thu	Thu06:00	1608
						05/01/2017 07:00	07:00	12	108	Thu	Thu07:00	1620
						05/01/2017 08:00	08:00	12	109	Thu	Thu08:00	1632
						05/01/2017 09:00	09:00	12	110	Thu	Thu09:00	1644
						05/01/2017 10:00	10:00	12	111	Thu	Thu10:00	1656
						05/01/2017 11:00	11:00	12	112	Thu	Thu11:00	1668
						05/01/2017 12:00	12:00	12	113	Thu	Thu12:00	1680
						05/01/2017 13:00	13:00	12	114	Thu	Thu13:00	1692
						05/01/2017 14:00	14:00	12	115	Thu	Thu14:00	1704
						05/01/2017 15:00	15:00	12	116	Thu	Thu15:00	1716
						05/01/2017 16:00	16:00	12	117	Thu	Thu16:00	1728
						05/01/2017 17:00	17:00	12	118	Thu	Thu17:00	1740
						05/01/2017 18:00	18:00	12	119	Thu	Thu18:00	1752
						05/01/2017 19:00	19:00	12	120	Thu	Thu19:00	1764
						05/01/2017 20:00	20:00	12	121	Thu	Thu20:00	1776
						05/01/2017 21:00	21:00	14	122	Thu	Thu21:00	1790
						05/01/2017 22:00	22:00	14	123	Thu	Thu22:00	1804
						05/01/2017 23:00	23:00	14	124	Thu	Thu23:00	1818
					fri	05/01/2017 23:59	00:00	14	125	Thu	Fri00:00	1832
						06/01/2017 01:00	01:00	14	127	Fri	Fri01:00	1846
						06/01/2017 02:00	02:00	14	128	Fri	Fri02:00	1860
						06/01/2017 03:00	03:00	14	129	Fri	Fri03:00	1874
						06/01/2017 04:00	04:00	14	130	Fri	Fri04:00	1888
						06/01/2017 05:00	05:00	14	131	Fri	Fri05:00	1902
						06/01/2017 06:00	06:00	14	132	Fri	Fri06:00	1916
						06/01/2017 07:00	07:00	12	133	Fri	Fri07:00	1928
						06/01/2017 08:00	08:00	12	134	Fri	Fri08:00	1940
						06/01/2017 09:00	09:00	12	135	Fri	Fri09:00	1952
						06/01/2017 10:00	10:00	12	136	Fri	Fri10:00	1964
						06/01/2017 11:00	11:00	12	137	Fri	Fri11:00	1976
						06/01/2017 12:00	12:00	12	138	Fri	Fri12:00	1988
						06/01/2017 13:00	13:00	12	139	Fri	Fri13:00	2000
						06/01/2017 14:00	14:00	12	140	Fri	Fri14:00	2012
						06/01/2017 15:00	15:00	12	141	Fri	Fri15:00	2024
						06/01/2017 16:00	16:00	12	142	Fri	Fri16:00	2036
						06/01/2017 17:00	17:00	12	143	Fri	Fri17:00	2048
						06/01/2017 18:00	18:00	12	144	Fri	Fri18:00	2060
						06/01/2017 19:00	19:00	12	145	Fri	Fri19:00	2072
						06/01/2017 20:00	20:00	12	146	Fri	Fri20:00	2084
						06/01/2017 21:00	21:00	14	147	Fri	Fri21:00	2098
						06/01/2017 22:00	22:00	14	148	Fri	Fri22:00	2112
						06/01/2017 23:00	23:00	14	149	Fri	Fri23:00	2126
					sat	06/01/2017 23:59	00:00	14	150	Fri	Sat00:00	2140
						07/01/2017 01:00	01:00	17	152	Sat	Sat01:00	2157
						07/01/2017 02:00	02:00	17	153	Sat	Sat02:00	2174
						07/01/2017 03:00	03:00	17	154	Sat	Sat03:00	2191
						07/01/2017 04:00	04:00	17	155	Sat	Sat04:00	2208
						07/01/2017 05:00	05:00	17	156	Sat	Sat05:00	2225
						07/01/2017 06:00	06:00	17	157	Sat	Sat06:00	2242
						07/01/2017 07:00	07:00	17	158	Sat	Sat07:00	2259
						07/01/2017 08:00	08:00	17	159	Sat	Sat08:00	2276
						07/01/2017 09:00	09:00	17	160	Sat	Sat09:00	2293
						07/01/2017 10:00	10:00	17	161	Sat	Sat10:00	2310
						07/01/2017 11:00	11:00	17	162	Sat	Sat11:00	2327
						07/01/2017 12:00	12:00	17	163	Sat	Sat12:00	2344
						07/01/2017 13:00	13:00	17	164	Sat	Sat13:00	2361
						07/01/2017 14:00	14:00	17	165	Sat	Sat14:00	2378
						07/01/2017 15:00	15:00	17	166	Sat	Sat15:00	2395
						07/01/2017 16:00	16:00	17	167	Sat	Sat16:00	2412
						07/01/2017 17:00	17:00	17	168	Sat	Sat17:00	2429
						07/01/2017 18:00	18:00	17	169	Sat	Sat18:00	2446
						07/01/2017 19:00	19:00	17	170	Sat	Sat19:00	2463
						07/01/2017 20:00	20:00	17	171	Sat	Sat20:00	2480
						07/01/2017 21:00	21:00	17	172	Sat	Sat21:00	2497
						07/01/2017 22:00	22:00	17	173	Sat	Sat22:00	2514
						07/01/2017 23:00	23:00	17	174	Sat	Sat23:00	2531
					satsun	07/01/2017 23:59	00:00	17	175	Sat	SunSat00:00	2548
						08/01/2017 01:00	01:00	25	177	Sun	SunSat01:00	2573
						08/01/2017 02:00	02:00	25	178	Sun	SunSat02:00	2598
						08/01/2017 03:00	03:00	25	179	Sun	SunSat03:00	2623
						08/01/2017 04:00	04:00	25	180	Sun	SunSat04:00	2648
						08/01/2017 05:00	05:00	25	181	Sun	SunSat05:00	2673
						08/01/2017 06:00	06:00	25	182	Sun	SunSat06:00	2698
						08/01/2017 07:00	07:00	25	183	Sun	SunSat07:00	2723
						08/01/2017 08:00	08:00	25	184	Sun	SunSat08:00	2748
						08/01/2017 09:00	09:00	25	185	Sun	SunSat09:00	2773
						08/01/2017 10:00	10:00	25	186	Sun	SunSat10:00	2798
						08/01/2017 11:00	11:00	25	187	Sun	SunSat11:00	2823
						08/01/2017 12:00	12:00	25	188	Sun	SunSat12:00	2848
						08/01/2017 13:00	13:00	25	189	Sun	SunSat13:00	2873
						08/01/2017 14:00	14:00	25	190	Sun	SunSat14:00	2898
						08/01/2017 15:00	15:00	25	191	Sun	SunSat15:00	2923
						08/01/2017 16:00	16:00	25	192	Sun	SunSat16:00	2948
						08/01/2017 17:00	17:00	25	193	Sun	SunSat17:00	2973
						08/01/2017 18:00	18:00	25	194	Sun	SunSat18:00	2998
						08/01/2017 19:00	19:00	25	195	Sun	SunSat19:00	3023
						08/01/2017 20:00	20:00	25	196	Sun	SunSat20:00	3048
						08/01/2017 21:00	21:00	25	197	Sun	SunSat21:00	3073
						08/01/2017 22:00	22:00	25	198	Sun	SunSat22:00	3098
						08/01/2017 23:00	23:00	25	199	Sun	SunSat23:00	3123
						08/01/2017 23:59	00:00	25	200	Sun	SunSat00:00	3148

<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[nitroxed]

Thanks for your help on this, however, the monetary values of the respective rates is not particularly important, it is the calculation (or summing) of the hours by shift category that is. Any chance you could make the content of your reply a little clearer ? You mentioned the helper table, however I'm not certain what the numbers are in the far right hand side ? Thanks.

 

[oldbrewer]

in stead of a dollar rate I could have put in an hours "figure" ie 1,1.3,1.5,2.0

then you would multiply base salary by hours.

The last but one column eg sun00:00 is just a concatenation of the day of the week with the time of the day, the last bit is eg satsun00:00 is to allow for a saturday start and sunday finish.

The last column is a running total of pay, so you deduct start day time pay from end day time pay.