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
[TABLE="width: 305"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Sheet 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Coulmn B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Processor[/TD]
[TD]Activity[/TD]
[TD]Processed on[/TD]
[TD]Minutes[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Call[/TD]
[TD]12/03/2019 04:10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 04:15[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 04:18[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]IT issue[/TD]
[TD]12/03/2019 04:08[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 04:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Call[/TD]
[TD]12/03/2019 05:01[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 05:10[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]IT issue[/TD]
[TD]12/03/2019 06:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Ticket[/TD]
[TD]12/03/2019 07:16[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 08:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 08:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 09:16[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 562"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Expected result sheet 1 only to count activity vs time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]Processor[/TD]
[TD]4 PM to 5 PM[/TD]
[TD]5 PM to 6 PM[/TD]
[TD]7 PM to 8 PM[/TD]
[TD]8 PM to 9 PM[/TD]
[TD]9 PM to 10 PM[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 720"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Expected result sheet 2, total time spent by processor on each activity for every hour[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD]Processor[/TD]
[TD]Activity[/TD]
[TD]4 PM to 5 PM[/TD]
[TD]5 PM to 6 PM[/TD]
[TD]7 PM to 8 PM[/TD]
[TD]8 PM to 9 PM[/TD]
[TD]9 PM to 10 PM[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Call[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Chat[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]IT issue[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Chat[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Call[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD]Chat[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]IT issue[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 305"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Sheet 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Coulmn B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Processor[/TD]
[TD]Activity[/TD]
[TD]Processed on[/TD]
[TD]Minutes[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Call[/TD]
[TD]12/03/2019 04:10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 04:15[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 04:18[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]IT issue[/TD]
[TD]12/03/2019 04:08[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 04:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Call[/TD]
[TD]12/03/2019 05:01[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 05:10[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]IT issue[/TD]
[TD]12/03/2019 06:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Ticket[/TD]
[TD]12/03/2019 07:16[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 08:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 08:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 09:16[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 562"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Expected result sheet 1 only to count activity vs time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]Processor[/TD]
[TD]4 PM to 5 PM[/TD]
[TD]5 PM to 6 PM[/TD]
[TD]7 PM to 8 PM[/TD]
[TD]8 PM to 9 PM[/TD]
[TD]9 PM to 10 PM[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 720"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Expected result sheet 2, total time spent by processor on each activity for every hour[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD]Processor[/TD]
[TD]Activity[/TD]
[TD]4 PM to 5 PM[/TD]
[TD]5 PM to 6 PM[/TD]
[TD]7 PM to 8 PM[/TD]
[TD]8 PM to 9 PM[/TD]
[TD]9 PM to 10 PM[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Call[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Chat[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]IT issue[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Chat[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Call[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD]Chat[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]IT issue[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
Thanks
Last edited: