Hello Team.. another request.. Been thru similar requests threads..none matched my requirement.
Based on the below RAW DATA, I want to know how many staff I have at any given time.
SAMPLE RAW DATA:
<colgroup><col><col><col><col span="7"></colgroup><tbody>
</tbody>
I have 90 such entries.
Each person works for 9 hours straight.
I don't bother about breaks.
Below are the shifts we operate:
SHIFT TIMINGS:
<colgroup><col><col><col></colgroup><tbody>
</tbody>
SUGGESTED OUTPUT: (Could be incorrect.. I did this manually!)
<colgroup><col width="80" span="8" style="width:60pt"> </colgroup><tbody>
</tbody>
I am looking for a formula like Sumproduct. Please avoid VBA .
Await your reply.
Thanks
martin
Based on the below RAW DATA, I want to know how many staff I have at any given time.
SAMPLE RAW DATA:
EMP ID | Emp Name | Supervisor | 14-Oct-18 | 15-Oct-18 | 16-Oct-18 | 17-Oct-18 | 18-Oct-18 | 19-Oct-18 | 20-Oct-18 |
1070273 | Thomas Moses | Martin Fernandes | OFF | OFF | Vacation | 20:30 | 20:30 | 20:30 | 20:30 |
1071115 | Allan Walker | Ricardo Bravo | OFF | OFF | 20:30 | 20:30 | 20:30 | 20:30 | 20:30 |
1069788 | Allison Moyet | Ricardo Bravo | 13:30 | OFF | OFF | 13:30 | 13:30 | 13:30 | 13:30 |
1073237 | Pink Floyd | Martin Fernandes | Vacation | 05:30 | Vacation | Vacation | Vacation | Vacation | Vacation |
1072766 | Phill Collins | Anson Palio | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 | OFF | OFF |
1071247 | Sam Richardson | Ricardo Bravo | 20:30 | 20:30 | 20:30 | 20:30 | 20:30 | OFF | OFF |
1070905 | Samantha Fox | Martin Fernandes | 20:30 | 20:30 | 20:30 | 20:30 | OFF | OFF | 20:30 |
1070272 | Anifa Sequeira | Anson Palio | OFF | 11:30 | 11:30 | 11:30 | 11:30 | 11:30 | OFF |
1067361 | Poison Ivy | Ricardo Bravo | Vacation | Vacation | Vacation | Vacation | Vacation | Vacation | Vacation |
1067150 | Anthony Stallon | Martin Fernandes | 07:30 | 07:30 | OFF | 07:30 | 07:30 | 07:30 | OFF |
1073477 | Joshua Fernandez | Anson Palio | OFF | OFF | 13:30 | 13:30 | 13:30 | 13:30 | 13:30 |
1067068 | Jude Patrick | Ricardo Bravo | 12:30 | 12:30 | OFF | OFF | 12:30 | 12:30 | 12:30 |
1073839 | Barry Heldt | Martin Fernandes | 18:30 | 18:30 | 18:30 | OFF | OFF | 18:30 | 18:30 |
1062698 | Ben Isaac | Anson Palio | 07:30 | 07:30 | 07:30 | OFF | OFF | 07:30 | 07:30 |
1072024 | Christopher R | Martin Fernandes | 11:30 | 11:30 | 11:30 | 11:30 | 11:30 | OFF | OFF |
1064043 | Clifford Das | Anson Palio | 12:30 | 12:30 | 12:30 | 12:30 | Vacation | OFF | OFF |
1073989 | Morten Harket | Ricardo Bravo | 16:30 | 16:30 | 16:30 | 16:30 | 16:30 | OFF | OFF |
1073533 | Delphinia Dkhar | Anson Palio | 16:30 | 16:30 | 16:30 | OFF | OFF | 16:30 | 16:30 |
1067367 | Cheryl Rodrigues | Martin Fernandes | 09:30 | 09:30 | 09:30 | 09:30 | Vacation | OFF | OFF |
<colgroup><col><col><col><col span="7"></colgroup><tbody>
</tbody>
I have 90 such entries.
Each person works for 9 hours straight.
I don't bother about breaks.
Below are the shifts we operate:
SHIFT TIMINGS:
Sr. No. | Start Time | End Time |
1 | 05:30 | 14:30 |
2 | 07:30 | 16:30 |
3 | 09:30 | 18:30 |
4 | 11:30 | 20:30 |
5 | 12:30 | 21:30 |
6 | 13:30 | 22:30 |
7 | 14:30 | 23:30 |
8 | 16:30 | 01:30 |
9 | 17:30 | 02:30 |
10 | 18:30 | 03:30 |
11 | 20:30 | 05:30 |
12 | 21:30 | 06:30 |
13 | 22:30 | 07:30 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
SUGGESTED OUTPUT: (Could be incorrect.. I did this manually!)
<colgroup><col><col><col><col span="5"></colgroup><tbody> </tbody> |
<colgroup><col width="80" span="8" style="width:60pt"> </colgroup><tbody>
</tbody>
I am looking for a formula like Sumproduct. Please avoid VBA .
Await your reply.
Thanks
martin