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
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.
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Perfect, thanks for all your help.
or
=MID("ABCA",MATCH(TIMEVALUE(MID(A2,15,12))*48,{0,9 ,25,41,48},1),1)
Posting Permissions
Reply With Quote
Bookmarks