Attendance Count per Interval

[Rhon Alvarez]

Hi Team,

need help in the attached file please.

required: to track down each agent logged in a particular interval using the Time Start column in a productive and non-productive state using the Timetable as reference.
issue : not reading the exact Pattern in the timetable ( there were #n/a, 9:00 am is giving result of pattern 56 instead of pattern 9

thanks in advance,

Rhon

 

[AlanSidman]

Your issue is that all your times are text and not real times. See attached where I changed the times to "time" and then joined the two tables in Power Query

 

[Rhon Alvarez]

Your issue is that all your times are text and not real times. See attached where I changed the times to "time" and then joined the two tables in Power Query

thanks @AlanSidman,

however upon checking, those start time with a fraction of a minute shows no pattern at all:
I.e. 9:04 should be in the intervals of 9:00 to 9:14 or pattern 9 ; 8:10 should be pattern 5 and so on...
is there still a way to have these fixed?

thanks once again for the reply.

 

[vletm]

This could solve without TimeTable-sheet eg like below
#1 add those cells K1 & L1 with real time as below
#2 write to cell I2 formulas as below