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

		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

 

[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

Harry	IT issue	12/03/2019 09:16	6
Harry	Ticket	12/03/2019 10:16	4
Harry	Chat	12/03/2019 11:16	6
Harry	Chat	12/03/2019 12:16	6
Harry	Chat	12/03/2019 12:16	6
Harry	Chat	12/03/2019 12:16	6

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Below are the results im getting

Processor	9 - 10	10 - 11	11 - 12	12 - 1
Peter	0	0	0	0
James	0	0	0	0
Paul	0	0	0	0
Helen	0	0	0	0
Harry	1	0	0	0

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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?

Processor

Activity

Processed on

Minutes

Processor

Start of Hour

End of Hour

Count

Processor

Activity

Start of Hour

End of Hour

Count

Minutes

Peter

Call

12/03/2019 04:10​

5​

Peter

04:00:00​

05:00:00​

3​

Peter

Call

04:00:00​

05:00:00​

1​

5​

Peter

Chat

12/03/2019 04:15​

6​

James

04:00:00​

05:00:00​

1​

Peter

Chat

04:00:00​

05:00:00​

2​

12​

Peter

Chat

12/03/2019 04:18​

6​

Paul

04:00:00​

05:00:00​

1​

James

IT issue

04:00:00​

05:00:00​

1​

5​

James

IT issue

12/03/2019 04:08​

5​

Peter

05:00:00​

06:00:00​

1​

Paul

Chat

04:00:00​

05:00:00​

1​

6​

Paul

Chat

12/03/2019 04:16​

6​

Helen

05:00:00​

06:00:00​

1​

Peter

Call

05:00:00​

06:00:00​

1​

5​

Peter

Call

12/03/2019 05:01​

5​

Harry

06:00:00​

07:00:00​

1​

Helen

Chat

05:00:00​

06:00:00​

1​

6​

Helen

Chat

12/03/2019 05:10​

6​

Harry

07:00:00​

08:00:00​

1​

Harry

IT issue

06:00:00​

07:00:00​

1​

6​

Harry

IT issue

12/03/2019 06:16​

6​

Harry

08:00:00​

09:00:00​

2​

Harry

Ticket

07:00:00​

08:00:00​

1​

4​

Harry

Ticket

12/03/2019 07:16​

4​

Harry

09:00:00​

10:00:00​

1​

Harry

Chat

08:00:00​

09:00:00​

2​

12​

Harry

Chat

12/03/2019 08:16​

6​

Harry

Chat

09:00:00​

10:00:00​

1​

6​

Harry

Chat

12/03/2019 08:16​

6​

Harry

Chat

12/03/2019 09:16​

6​

 

[exceluser9]

Hi Sandy,

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