• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate total working hours

Meghna2020

New Member
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 IDConnection/Reconnection/Disconnection timeTime Stamp
ABCDCONNECTED3/30/2020 11:11
ABCDDISCONNECTED3/30/2020 11:25
ABCDRECONNECTED3/30/2020 11:40
ABCDDISCONNECTED3/30/2020 18:20
ABCDRECONNECTED3/30/2020 19:21
ABCDDISCONNECTED3/30/2020 20:40
 
Using Power Query, I was able to solve with the following Mcode. File attached for your review.

Code:
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"
 

Attachments

  • TimeSummary.xlsx
    19.5 KB · Views: 7
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.66749


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

Code:
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"
 
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.
 
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
 
Back
Top