Please see the attached sheet, i need to calculate the no events in each shift
Shift A - 8.31 PM - 4.30 AM
Shift B - 4.31 AM - 12.30 PM
Shift C - 12.31 PM - 8.30 PM
Printable View
Please see the attached sheet, i need to calculate the no events in each shift
Shift A - 8.31 PM - 4.30 AM
Shift B - 4.31 AM - 12.30 PM
Shift C - 12.31 PM - 8.30 PM
If you use the following formula in B2, you would get the Shift name/code.
=LOOKUP(MOD(SUBSTITUTE(A2,",","",2),1),{0,4.5,12.5 ,20.5,24}/24,{"A","B","C","A","B"})
OR
=LOOKUP(--REPLACE(A2,1,14,""),{0,4.5,12.5,20.5,24}/24,{"A","B","C","A","B"})
And you can use a pivot to get the count of events in each shift.
Perfect, thanks for all your help.
or
=MID("ABCA",MATCH(TIMEVALUE(MID(A2,15,12))*48,{0,9 ,25,41,48},1),1)