Hello guys,
I am facing a problem for which I couldn't find anything on this forum so far.
I am trying to find how many agents with a certain skill are available during a 30 minute time frame based on their schedule(Table1). An agent can have multiple skills and those should be reflected in Table2.
Table1
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]row/col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Agent[/TD]
[TD]IN[/TD]
[TD]OUT[/TD]
[TD]skill1[/TD]
[TD]skill2[/TD]
[TD]skill3[/TD]
[TD]skill4[/TD]
[TD]skill5[/TD]
[TD]skill6[/TD]
[TD]skill7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AGENT1[/TD]
[TD]9:00 AM[/TD]
[TD]5:30 PM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AGENT2[/TD]
[TD]9:00 AM[/TD]
[TD]5:30 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AGENT3[/TD]
[TD]9:00 AM[/TD]
[TD]5:30 PM[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AGENT4[/TD]
[TD]12:00 PM[/TD]
[TD]8:30 PM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AGENT5[/TD]
[TD]12:00 PM[/TD]
[TD]8:30 PM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AGENT6[/TD]
[TD]6:00 PM[/TD]
[TD]2:30 AM[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]AGENT7[/TD]
[TD]6:00 PM[/TD]
[TD]2:30 AM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]AGENT8[/TD]
[TD]5:00 PM[/TD]
[TD]1:30 AM[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
Table2
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]row/col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Skill1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Time frame[/TD]
[TD]Logged[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]00:00-00:30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]00:30-01:00[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01:00-01:30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]01:30-02:00[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
So Table2 actually is created for each skill and each 30 minute timeframe.
Considering that each agent works every day from Table1 - B2 to C2 and he can have skills from D2:J2.
So on Table2-B3 I should have a formula that counts all Agents that are available between 00:00 to 00:30 and that have on column D something that is not blank considering their time on IN B:B and OUT C:C from table1.
Thanks,
Razvan
I am facing a problem for which I couldn't find anything on this forum so far.
I am trying to find how many agents with a certain skill are available during a 30 minute time frame based on their schedule(Table1). An agent can have multiple skills and those should be reflected in Table2.
Table1
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]row/col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Agent[/TD]
[TD]IN[/TD]
[TD]OUT[/TD]
[TD]skill1[/TD]
[TD]skill2[/TD]
[TD]skill3[/TD]
[TD]skill4[/TD]
[TD]skill5[/TD]
[TD]skill6[/TD]
[TD]skill7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AGENT1[/TD]
[TD]9:00 AM[/TD]
[TD]5:30 PM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AGENT2[/TD]
[TD]9:00 AM[/TD]
[TD]5:30 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AGENT3[/TD]
[TD]9:00 AM[/TD]
[TD]5:30 PM[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AGENT4[/TD]
[TD]12:00 PM[/TD]
[TD]8:30 PM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AGENT5[/TD]
[TD]12:00 PM[/TD]
[TD]8:30 PM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AGENT6[/TD]
[TD]6:00 PM[/TD]
[TD]2:30 AM[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]AGENT7[/TD]
[TD]6:00 PM[/TD]
[TD]2:30 AM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]AGENT8[/TD]
[TD]5:00 PM[/TD]
[TD]1:30 AM[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
Table2
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]row/col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Skill1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Time frame[/TD]
[TD]Logged[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]00:00-00:30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]00:30-01:00[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01:00-01:30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]01:30-02:00[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
So Table2 actually is created for each skill and each 30 minute timeframe.
Considering that each agent works every day from Table1 - B2 to C2 and he can have skills from D2:J2.
So on Table2-B3 I should have a formula that counts all Agents that are available between 00:00 to 00:30 and that have on column D something that is not blank considering their time on IN B:B and OUT C:C from table1.
Thanks,
Razvan