Shift and distance calculation

[RSnap3232]

hello All.

So I have a spread sheet where it has everyones start times (Date and Time, Column I and J) and the finish time (Date and Time, Column K and L) and the distance they have travelled in column N.

I have it so the spreadsheet splits up their shifts between our two shift groups 7am to 7pm (day shift) then 7pm to 7am (night shift) however some of these shifts are staggered therefore a day shift worker can end up working into the night shift, is there a way I can calculate the distance between the two shift groups depending on their start and finish time then working out how much of the distance they travelled in that shift is day then the rest night?

ie they start at 4pm and finish at 2am the next day therefore 30% of the distance they travelled is day time and 70% would be night time shift

The shift I currently use is versions of;
=SUMIFS('week 4'!$N:$N,'week 4'!$I:$I,C4,'week 4'!$J:$J,">=07:00",'week 4'!$J:$J,"<=19:00")

 

[maabadi]

Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

 

[RSnap3232]

Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

I am working off a work laptop at the moment so there are alot of restrictions with regards to uploading and sharing and unable to use anything like this at the moment

 

[maabadi]

Try this:

Example Sheet.xlsx
	A	B	C					H	I	J	K	L	M	N	O	P	Q	R
1																Day Shift	Night Shift
2									2/5/2021	8:00:00	2/5/2021	14:00:00		400		6:00:00	0:00:00
3									2/5/2021	11:00:00	2/5/2021	17:00:00		420		6:00:00	0:00:00
4			2/5/2021						2/5/2021	14:00:00	2/5/2021	20:00:00		440		5:00:00	1:00:00
5									2/5/2021	17:00:00	2/5/2021	23:00:00		460		2:00:00	4:00:00
6									2/5/2021	20:00:00	2/6/2021	2:00:00		480		0:00:00	6:00:00
7									2/5/2021	23:00:00	2/6/2021	5:00:00		500		0:00:00	6:00:00
8									2/6/2021	2:00:00	2/6/2021	8:00:00		520		1:00:00	5:00:00
9									2/6/2021	5:00:00	2/6/2021	11:00:00		540		4:00:00	2:00:00
10									2/6/2021	8:00:00	2/6/2021	14:00:00		560		6:00:00	0:00:00
11									2/6/2021	11:00:00	2/6/2021	17:00:00		580		6:00:00	0:00:00
12
13										Total Day Distance
14										1340
15
Sheet1

Cell Formulas
Range	Formula
P2:P11	P2	=IF(IF((J2>=7/24)*(L2<19/24),L2-J2,IF((J2>=7/24)*(L2>=19/24),19/24-J2,IF((J2<7/24)*(L2>7/24),L2-7/24,0)))<0,0,IF((J2>=7/24)*(L2<19/24),L2-J2,IF((J2>=7/24)*(L2>=19/24),19/24-J2,IF((J2<7/24)*(L2>7/24),L2-7/24,0))))
Q2:Q11	Q2	=K2+L2-I2-J2-P2
J14	J14	=SUMPRODUCT(($N$2:$N$11)*($I$2:$I$11=$C$4)*($P$2:$P$11/($P$2:$P$11+$Q$2:$Q$11)))