Calculate total working hours

[Meghna2020]

I have data as given below and need to calculate the exact time that is spent by an agent in this entire journey, with respect to connection, re-connection time and disconnection time. This needs to be done for nearly 1000 agents and in excel either using excel/VBA. Please help. Data is as given below:

EMP ID	Connection/Reconnection/Disconnection time	Time Stamp
ABCD	CONNECTED	3/30/2020 11:11
ABCD	DISCONNECTED	3/30/2020 11:25
ABCD	RECONNECTED	3/30/2020 11:40
ABCD	DISCONNECTED	3/30/2020 18:20
ABCD	RECONNECTED	3/30/2020 19:21
ABCD	DISCONNECTED	3/30/2020 20:40

 

[AlanSidman]

Using Power Query, I was able to solve with the following Mcode. File attached for your review.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MP ID", type text}, {"Connection/Reconnection/Disconnection time", type text}, {"Time Stamp", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Start([#"Connection/Reconnection/Disconnection time"],3)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if[Custom]="DIS" then [Time Stamp] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] <> "DIS")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Time Stamp", "Connect"}, {"Custom.1", "Disconnect"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Custom"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each [Disconnect]-[Connect]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Connection/Reconnection/Disconnection time", "Connect", "Disconnect"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"MP ID"]), "MP ID", "Custom", List.Sum)
in
    #"Pivoted Column"

 

[Meghna2020]

Thanks for you help Alan on this, as suggested by you have installed power query, I am using excel 2013 and after entering the code given by you, I am getting the attached error. I have never used power query so would need your help on this. Really appreciate your help. Thanks a lot.

Using Power Query, I was able to solve with the following Mcode. File attached for your review.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MP ID", type text}, {"Connection/Reconnection/Disconnection time", type text}, {"Time Stamp", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Start([#"Connection/Reconnection/Disconnection time"],3)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if[Custom]="DIS" then [Time Stamp] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] <> "DIS")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Time Stamp", "Connect"}, {"Custom.1", "Disconnect"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Custom"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each [Disconnect]-[Connect]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Connection/Reconnection/Disconnection time", "Connect", "Disconnect"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"MP ID"]), "MP ID", "Custom", List.Sum)
in
    #"Pivoted Column"

 

[AlanSidman]

It appears that the table you are loading into PQ may have a different name from Table1. Change that in the Mcode you insert to the proper name. You may want to buy the book "M is for (Data) Monkey" which is an excellent primer for learing PQ.

 

[Meghna2020]

Thank you so much Alan. I added my data in the file you attached and is working for me. Was able to get the results. Thank you so much, really appreciate it. Also would try to get the book suggested by you. Thanks