Time Duration

[lordsugar]

Hello Peeps,

Good day to all. I would like to seek help on how can I sum of duration of hours. I attached a file in here. What I want to do is to sum the total number of pause, online and offline in my data. Each data has its time stamp so i think it can be done but not sure where to start

https://drive.google.com/open?id=1YlCJp_QNSue27w60kf2fgXxGSGnU9tUG

Hope you can help on this one : )

Thanks

lordsugar

 

[sandy666]

just for fun I did

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]agentId[/td][td=bgcolor:#70AD47]pause[/td][td=bgcolor:#70AD47]online[/td][td=bgcolor:#70AD47]offline[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]WC11[/td][td=bgcolor:#E2EFDA]

3.15:55:47​

[/td][td=bgcolor:#E2EFDA]

3.07:54:52​

[/td][td=bgcolor:#E2EFDA]

0.23:58:15​

[/td][/tr]
[/table]
format: d.hh:mm:ss

but could you show expected result from your example?

 

[lordsugar]

just for fun I did

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]agentId[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]pause[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]online[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]offline[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]WC11[/TD]
[TD="bgcolor: #E2EFDA"]

3.15:55:47​

[/TD]
[TD="bgcolor: #E2EFDA"]

3.07:54:52​

[/TD]
[TD="bgcolor: #E2EFDA"]

0.23:58:15​

[/TD]
[/TR]
</tbody>[/TABLE]

format: d.hh:mm:ss

but could you show expected result from your example?

exactly what im looking for! How did you get this? thanks Sandy!

 

[sandy666]

you will need PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"agentId", type text}, {"agentStatus", type text}, {"timestamp", type text}, {"gmtDate", type datetime}, {"localDate", type datetimezone}, {"time", type time}, {"date", type date}}),
    #"Inserted Time" = Table.AddColumn(#"Changed Type", "Time.1", each DateTime.Time([gmtDate]), type time),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Inserted Time", "Subtraction", each [time] - [Time.1], type duration),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Time Subtraction",{{"Subtraction", type duration}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",1),
    #"Grouped Rows" = Table.Group(#"Removed Bottom Rows", {"agentId", "agentStatus"}, {{"Count", each List.Sum([Subtraction]), type duration}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[agentStatus]), "agentStatus", "Count", List.Sum)
in
    #"Pivoted Column"[/SIZE]

 

[lordsugar]

Thanks Sandy - is there a way to do it by formula rather than power query?

 

[sandy666]

You are welcome

I don't know, maybe someone else

have a nice day

 

[lordsugar]

Thanks again Sandy! Just an update

Below is the link for the raw file. What I'm trying to achieve is to calculate the total # of online, pause and offline. The complexities happen when the status were stack into column and the time on the other column. Just want to understand how can we achieve it by formula in excel. The intended results is in the drive as well.

Thanks guys! UP on this post!

https://drive.google.com/open?id=1EujHGX-oAnlbR4hIOXXcQzVDvTGupvp_