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

 

[sandy666]

sure but this is PowerQuery (Get&Transform) not a formula

if you are interested here is:

[SIZE=1]// First
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Processor", type text}, {"Activity", type text}, {"Processed on", type time}, {"Minutes", Int64.Type}}),
    Start = Table.AddColumn(Type, "Start of Hour", each Time.StartOfHour([Processed on]), type time),
    End = Table.AddColumn(Start, "End of Hour", each Time.EndOfHour([Processed on]), type time),
    Group = Table.Group(End, {"Processor", "Start of Hour", "End of Hour"}, {{"Count", each Table.RowCount(_), type number}})
in
    Group[/SIZE]

[SIZE=1]// Second
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Processor", type text}, {"Activity", type text}, {"Processed on", type time}, {"Minutes", Int64.Type}}),
    Start = Table.AddColumn(Type, "Start of Hour", each Time.StartOfHour([Processed on]), type time),
    End = Table.AddColumn(Start, "End of Hour", each Time.EndOfHour([Processed on]), type time),
    Group = Table.Group(End, {"Processor", "Activity", "Start of Hour", "End of Hour"}, {{"Count", each Table.RowCount(_), type number}, {"Minutes", each List.Sum([Minutes]), type number}})
in
    Group[/SIZE]

example file

 

[kweaver]

PowerQuery is pretty nice!

Using my formula version and some formatting (blank 0), you can get this:

 

[exceluser9]

Hi Sandy,

How do i use power query?

Can it be done through vba instead?

 

[exceluser9]

Hi Kweaver.

Can we amend 18 - 19 to 6 - 7? i will be showing the data only from 12 PM to 10 PM.

And would require a formula formula to should hourly data and for two hours.

The table which sandy666 gave is my requirement

 

[sandy666]

How do i use power query?

You've file with everything.
What is your excel version?

Can it be done through vba instead?

I don't know. I hate vba and I am far far away from this so I can't help

 

[exceluser9]

Hi Sandy,

Excel version 2016

 

[sandy666]

So,
Data - Show Queries - on the right side you'll see Workbook Queries tables: choose the one you want to have and dbl click - in new window (PowerrQuery Editor) find Advanced Editor and open it then you'll see the same code as in the post.

How to use? Simply add more data to your source table then right click on green table and select refresh

edit:
or just use Ctrl+Alt+F5

 

[exceluser9]

Hi Sandy,

Im not finding below details could you give screenshot? Im not finding an option to copy paste this code

choose the one you want to have and dbl click - in new window (PowerrQuery Editor) find Advanced Editor and open it then you'll see the same code as in the post.

 

[sandy666]

do you see Workbook Queries on the right?
if yes double click on table called First
what you see after that?

in a meantime see:

• What is PowerQuery
• Getting Started with Get & Transform
• Microsoft Power Query for Excel
• Excel Power Query (Get & Transform) Basic Tutorial

 

[exceluser9]

Hi Sandy,

I do not see any table called first