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