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

 

[kweaver]

Try this on for size:

Fill down and across the B17 and C23 formulas in their respective brackets.

 

[exceluser9]

Hi Keweaver,

Thank you for response, im not able to copy formula since its in image.

Can you provide formula pls?

 

[exceluser9]

Also original data is in sheet 3, will this look up into sheet 3?

And i want the formula to show the progress for 12 hour. Once is hourly basis and another once in 2 hours

 

[kweaver]

B7:

=SUMPRODUCT((--($A$2:$A$13=$A17))*(--(HOUR($C$2:$C$13)>=1*LEFT(B$16,1)))*(--(HOUR($C$2:$C$13)<1*1+LEFT(B$16,1))))

and filled...

C23:

=SUMPRODUCT((--($A$2:$A$13=$A24))*(--($B$2:$B$13=$B24))*(--(HOUR($C$2:$C$13)>=1*LEFT(C$23,1)))*(--(HOUR($C$2:$C$13)<1*1+LEFT(C$23,1)))*$D$2:$D$13)

and filled...

If the sheets vary, enter the sheet name(s) before the cell reference, e.g., Sheet1!$A24

 

[exceluser9]

Hi Kweaver,

I have tried the code and it formula didnt work fine from 10 - 11, 11- 12, 12 - 1 properly

[TABLE="width: 376"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Harry[/TD]
[TD]IT issue[/TD]
[TD]12/03/2019 09:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Ticket[/TD]
[TD]12/03/2019 10:16[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 11:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 12:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 12:16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Chat[/TD]
[TD]12/03/2019 12:16[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Below are the results im getting

[TABLE="width: 469"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Processor[/TD]
[TD]9 - 10[/TD]
[TD]10 - 11[/TD]
[TD]11 - 12[/TD]
[TD]12 - 1[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Could you help pls.

Thanks

 

[kweaver]

Sorry. That's because I cheated and assumed you were always starting with a single digit start hour. I haven't tested it on my sample, but suspect if you change the references to the "LEFT" function to 2 rather than 1 it should work.
BTW: note that a time such as 3 should be entered differently if you mean 3 AM vs PM. Then the testing has to be changed as well because 3 is AM and 3 PM would be represented by 15.

If I have a few minutes, I'll write that unless you feel OK doing it yourself.

B17, etc.:

=SUMPRODUCT((--($A$2:$A$13=$A17))*(--(HOUR($C$2:$C$13)>=1*LEFT(B$16,2)))*(--(HOUR($C$2:$C$13)<1*1+LEFT(B$16,2))))

C23, etc.:

=SUMPRODUCT((--($A$2:$A$13=$A24))*(--($B$2:$B$13=$B24))*(--(HOUR($C$2:$C$13)>=1*LEFT(C$23,2)))*(--(HOUR($C$2:$C$13)<1*1+LEFT(C$23,2)))*$D$2:$D$13)

I then made H23: 18 - 19 (for 6 PM - 7 PM)
and specifically adjusted the original date/time column C to have AM and PM distinctions.

 

[exceluser9]

Thanks mate.

If I have a few minutes, I'll write that unless you feel OK doing it yourself.

Can you write this as well mate?

Thanks

 

[sandy666]

If I understood correct you want something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Processor[/td][td=bgcolor:#5B9BD5]Activity[/td][td=bgcolor:#5B9BD5]Processed on[/td][td=bgcolor:#5B9BD5]Minutes[/td][td][/td][td=bgcolor:#70AD47]Processor[/td][td=bgcolor:#70AD47]Start of Hour[/td][td=bgcolor:#70AD47]End of Hour[/td][td=bgcolor:#70AD47]Count[/td][td][/td][td=bgcolor:#70AD47]Processor[/td][td=bgcolor:#70AD47]Activity[/td][td=bgcolor:#70AD47]Start of Hour[/td][td=bgcolor:#70AD47]End of Hour[/td][td=bgcolor:#70AD47]Count[/td][td=bgcolor:#70AD47]Minutes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Peter[/td][td=bgcolor:#DDEBF7]Call[/td][td=bgcolor:#DDEBF7]

12/03/2019 04:10​

[/td][td=bgcolor:#DDEBF7]

5​

[/td][td][/td][td=bgcolor:#E2EFDA]Peter[/td][td=bgcolor:#E2EFDA]

04:00:00​

[/td][td=bgcolor:#E2EFDA]

05:00:00​

[/td][td=bgcolor:#E2EFDA]

3​

[/td][td][/td][td=bgcolor:#E2EFDA]Peter[/td][td=bgcolor:#E2EFDA]Call[/td][td=bgcolor:#E2EFDA]

04:00:00​

[/td][td=bgcolor:#E2EFDA]

05:00:00​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Peter[/td][td]Chat[/td][td]

12/03/2019 04:15​

[/td][td]

6​

[/td][td][/td][td]James[/td][td]

04:00:00​

[/td][td]

05:00:00​

[/td][td]

1​

[/td][td][/td][td]Peter[/td][td]Chat[/td][td]

04:00:00​

[/td][td]

05:00:00​

[/td][td]

2​

[/td][td]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Peter[/td][td=bgcolor:#DDEBF7]Chat[/td][td=bgcolor:#DDEBF7]

12/03/2019 04:18​

[/td][td=bgcolor:#DDEBF7]

6​

[/td][td][/td][td=bgcolor:#E2EFDA]Paul[/td][td=bgcolor:#E2EFDA]

04:00:00​

[/td][td=bgcolor:#E2EFDA]

05:00:00​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]James[/td][td=bgcolor:#E2EFDA]IT issue[/td][td=bgcolor:#E2EFDA]

04:00:00​

[/td][td=bgcolor:#E2EFDA]

05:00:00​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]James[/td][td]IT issue[/td][td]

12/03/2019 04:08​

[/td][td]

5​

[/td][td][/td][td]Peter[/td][td]

05:00:00​

[/td][td]

06:00:00​

[/td][td]

1​

[/td][td][/td][td]Paul[/td][td]Chat[/td][td]

04:00:00​

[/td][td]

05:00:00​

[/td][td]

1​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Paul[/td][td=bgcolor:#DDEBF7]Chat[/td][td=bgcolor:#DDEBF7]

12/03/2019 04:16​

[/td][td=bgcolor:#DDEBF7]

6​

[/td][td][/td][td=bgcolor:#E2EFDA]Helen[/td][td=bgcolor:#E2EFDA]

05:00:00​

[/td][td=bgcolor:#E2EFDA]

06:00:00​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]Peter[/td][td=bgcolor:#E2EFDA]Call[/td][td=bgcolor:#E2EFDA]

05:00:00​

[/td][td=bgcolor:#E2EFDA]

06:00:00​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Peter[/td][td]Call[/td][td]

12/03/2019 05:01​

[/td][td]

5​

[/td][td][/td][td]Harry[/td][td]

06:00:00​

[/td][td]

07:00:00​

[/td][td]

1​

[/td][td][/td][td]Helen[/td][td]Chat[/td][td]

05:00:00​

[/td][td]

06:00:00​

[/td][td]

1​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Helen[/td][td=bgcolor:#DDEBF7]Chat[/td][td=bgcolor:#DDEBF7]

12/03/2019 05:10​

[/td][td=bgcolor:#DDEBF7]

6​

[/td][td][/td][td=bgcolor:#E2EFDA]Harry[/td][td=bgcolor:#E2EFDA]

07:00:00​

[/td][td=bgcolor:#E2EFDA]

08:00:00​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]Harry[/td][td=bgcolor:#E2EFDA]IT issue[/td][td=bgcolor:#E2EFDA]

06:00:00​

[/td][td=bgcolor:#E2EFDA]

07:00:00​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Harry[/td][td]IT issue[/td][td]

12/03/2019 06:16​

[/td][td]

6​

[/td][td][/td][td]Harry[/td][td]

08:00:00​

[/td][td]

09:00:00​

[/td][td]

2​

[/td][td][/td][td]Harry[/td][td]Ticket[/td][td]

07:00:00​

[/td][td]

08:00:00​

[/td][td]

1​

[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Harry[/td][td=bgcolor:#DDEBF7]Ticket[/td][td=bgcolor:#DDEBF7]

12/03/2019 07:16​

[/td][td=bgcolor:#DDEBF7]

4​

[/td][td][/td][td=bgcolor:#E2EFDA]Harry[/td][td=bgcolor:#E2EFDA]

09:00:00​

[/td][td=bgcolor:#E2EFDA]

10:00:00​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]Harry[/td][td=bgcolor:#E2EFDA]Chat[/td][td=bgcolor:#E2EFDA]

08:00:00​

[/td][td=bgcolor:#E2EFDA]

09:00:00​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Harry[/td][td]Chat[/td][td]

12/03/2019 08:16​

[/td][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Harry[/td][td]Chat[/td][td]

09:00:00​

[/td][td]

10:00:00​

[/td][td]

1​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Harry[/td][td=bgcolor:#DDEBF7]Chat[/td][td=bgcolor:#DDEBF7]

12/03/2019 08:16​

[/td][td=bgcolor:#DDEBF7]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Harry[/td][td]Chat[/td][td]

12/03/2019 09:16​

[/td][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[exceluser9]

Hi Sandy,

Yes you are right and the the table which you gave looks amazing, could you share me the formula please?