Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
just for fun I did

agentIdpauseonlineoffline
WC11
3.15:55:47​
3.07:54:52​
0.23:58:15​
format: d.hh:mm:ss

but could you show expected result from your example?
 
Last edited:
Upvote 0
just for fun I did

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]agentId[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]pause[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]online[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]offline[/COLOR]
WC11
3.15:55:47​
3.07:54:52​
0.23:58:15​

<tbody>
</tbody>

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!
 
Upvote 0
you will need PowerQuery

Code:
[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]
 
Upvote 0
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_
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top