exceluser9
Active Member
- Joined
- Jun 27, 2015
- Messages
- 388
Hi Team,
I have below data and I would require a formula to see how many activity each person has processed in an hour, for every hour I would like to show the progress in different table. I want to see the progress for 12 hours. I input the processor name in column A in sheet 1 and 2 to get the results
<tbody>
</tbody>
<tbody>
</tbody>
Also I would require one more formula to see how many minutes each person spent on the activity in each hour based on the minutes provided in minutes coulmn D
<tbody>
</tbody>
Thanks
I have below data and I would require a formula to see how many activity each person has processed in an hour, for every hour I would like to show the progress in different table. I want to see the progress for 12 hours. I input the processor name in column A in sheet 1 and 2 to get the results
Sheet 3 | |||
Column A | Coulmn B | Column C | Column D |
Processor | Activity | Processed on | Minutes |
Peter | Call | 12/03/2019 04:10 | 5 |
Peter | Chat | 12/03/2019 04:15 | 6 |
Peter | Chat | 12/03/2019 04:18 | 6 |
James | IT issue | 12/03/2019 04:08 | 5 |
Paul | Chat | 12/03/2019 04:16 | 6 |
Peter | Call | 12/03/2019 05:01 | 5 |
Helen | Chat | 12/03/2019 05:10 | 6 |
Harry | IT issue | 12/03/2019 06:16 | 6 |
Harry | Ticket | 12/03/2019 07:16 | 4 |
Harry | Chat | 12/03/2019 08:16 | 6 |
Harry | Chat | 12/03/2019 08:16 | 6 |
Harry | Chat | 12/03/2019 09:16 | 6 |
<tbody>
</tbody>
Expected result sheet 1 only to count activity vs time | |||||
Column A | Column B | Column C | Column D | Column E | Column F |
Processor | 4 PM to 5 PM | 5 PM to 6 PM | 7 PM to 8 PM | 8 PM to 9 PM | 9 PM to 10 PM |
Peter | 3 | 1 | |||
James | 1 | ||||
Paul | 1 | ||||
Helen | 1 | ||||
Harry | 1 | 1 | 2 | 1 |
<tbody>
</tbody>
Also I would require one more formula to see how many minutes each person spent on the activity in each hour based on the minutes provided in minutes coulmn D
Expected result sheet 2, total time spent by processor on each activity for every hour | ||||||
Column A | Column B | Column C | Column D | Column E | Column F | Column G |
Processor | Activity | 4 PM to 5 PM | 5 PM to 6 PM | 7 PM to 8 PM | 8 PM to 9 PM | 9 PM to 10 PM |
Peter | Call | 5 | ||||
Peter | Chat | 12 | ||||
James | IT issue | 5 | ||||
Paul | Chat | 6 | ||||
Peter | Call | 5 | ||||
Helen | Chat | 6 | ||||
Harry | IT issue | 6 | ||||
Harry | Chat | 12 | ||||
Harry | Chat | 6 |
<tbody>
</tbody>
Thanks
Last edited: