Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

sandy666

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

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

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