Calculating hours between columns

[exceluser9]

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

 

[exceluser9]

Hi All,

Can we not do in simple?

i.e Formula / VBA?

 

[sandy666]

what is your REAL excel version ?

or for Students?

btw. are you trying instruction from my post on downloaded example file or on new blank file?

 

[sandy666]

here is a video How To
it's as easy as making babies