Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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,218,923
Messages
6,145,243
Members
450,604
Latest member
ericmwr

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