Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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:

lordsugar

New Member
Joined
Apr 8, 2015
Messages
18
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!
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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]
 

lordsugar

New Member
Joined
Apr 8, 2015
Messages
18

ADVERTISEMENT

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

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
You are welcome

I don't know, maybe someone else

have a nice day
 

lordsugar

New Member
Joined
Apr 8, 2015
Messages
18
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_
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,903
Messages
5,834,312
Members
430,276
Latest member
legalcriminal015

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
Top