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

 

[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