Extract Time in 24H format from Date and time.

[zorro]

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

[Excel Fox]

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.

[zorro]

Perfect, thanks for all your help.

[snb]

or

=MID("ABCA",MATCH(TIMEVALUE(MID(A2,15,12))*48,{0,9,25,41,48}, 1),1)