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]

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.

Haha excel problems do tend to grow! The PowerQuery solution in this instance may be better (but im not too familiar with it).

As a formula solution, this should work:

=TIME(IF(RIGHT(A2,2)="PM",MID(A2,13,2)+12,MID(A2,13,2)),MID(A2,16,2),MID(A2,19,2))

Let me know
Caleeco

 

[shibigoku]

Hi Caleeco, you're such a great help. It works except for 12 pm. I think it's adding 12 hours so it becomes 12 am is that correct?

 

[Caleeco]

Hi Caleeco, you're such a great help. It works except for 12 pm. I think it's adding 12 hours so it becomes 12 am is that correct?

Ah so close! Ok, let me take another look... and see if there is a different approach we can take.

Give me a few mins!

Caleeco ✌

 

[FormR]

Hi, another option to try.

=0+MID(A2,13,11)

Book1
	A	B
2	05/27/2020, 01:14:06 PM	1:14 PM
3	05/27/2020, 12:00:00 PM	12:00 PM
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=0+MID(A2,13,11)

 

[Caleeco]

Hi, another option to try.

=0+MID(A2,13,11)

Book1
	A	B
2	05/27/2020, 01:14:06 PM	1:14 PM
3	05/27/2020, 12:00:00 PM	12:00 PM
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=0+MID(A2,13,11)

Probably as good as it's going to get! ✌

 

[shibigoku]

Hi guys,
That did it. Thank you both.

 

[alansidman]

@shibigoku
Did you try the Query solution? As Little Eddie said "Easy Peazy."

 

[dcardno]

You have the formula that Caleeco provided up a couple of posts:

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

Now you have to test whether the last two characters in the string are "AM" (do nothing) or "PM" (add 12 hours):

=TIME(MID(A1,13,2),MID(A1,16,2),MID(A1,19,2)) + IF(RIGHT(A1, 2) = "PM", 0.5, 0)