Separating data in a cell

[shibigoku]

Hi,

I work at a call center and we have changed our phone service provider. Unfortunately, the new call reporting system is missing some of the reports we are used too.
We have all the calls data and with pivot tables I managed to get most of the reports such as average talk time etct.
What brought me here is that I was looking for a way to have the data displayed in 30 min intervals and I found it here : Grouping by time in pivot table
However, before I can try this method, the current reports give us the Call start time of each call in a single cell like this : 05/27/2020, 10:55:47 AM
How do I extract just the time, in this case it would be 10:55:47?

Thanks

 

[Caleeco]

Hello,

Given that format, I assume these are text values you're working with. Provided each cell is in the same format/spacing you could just use a MID function

=MID(A1,12,10)

^ Assumes your date/time is in Cell A1

Hope that helps
Caleeco

 

[shibigoku]

Hi Caleeco,

That's it! Thanks!

 

[Caleeco]

No problem, glad I could help ✌

 

[shibigoku]

I have another problem. It seems like excel doesn't consider the extracted time as Time. Even if I change the format to time, it doesn't work. I get a value error with any formula I use with the extracted time.

 

[Caleeco]

Ah ok, try this instead then. It should produce a true TIME value

=TIME(MID(A1,13,2),MID(A1,16,2),MID(A1,19,2))

Let me know if it works

Cheers
Caleeco

 

[shibigoku]

Now it works like a charm. Thanks again

 

[Caleeco]

No worries, thanks for letting me know ✌

 

[alansidman]

Another option is to bring your data into Power Query. In PQ, there is the option to change the data types. It not only changes the format but the type. Here is the Mcode for doing such.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type time}})
in
    #"Changed Type1"

 

[shibigoku]

Hi I just realised that I now have a new problem. The new time is always in AM with this method.
I should have mentionned that PM time is displayed like this in the cell : 05/27/2020, 01:14:06 PM therefore when I extract the time I get 01:14:06 AM.
This is harder than I thought.