Extract Date & Time Attributes into separate cells

[MOCCOM]

I have a report that outputs date-time stamps in the following format: July 13th 2017, 11:40:21.553, and need to extract Day, Month, Year and Time in separate columns and then recombine them to create a valid date-time stamp. Please help!


Sample below illustrates the consistency of the strings - notice variations occur in terms of 1. length of characters in the month name 2. characters following the date i.e. st, nd, th

April 24th 2017, 11:40:29.703
August 1st 2017, 10:10:23.773
August 2nd 2017, 12:08:33.933

 

[Roxxien]

The solution is simple IF Excel detect that as a date and come with 2 choice

1.
A1 : where your data is
B1 : Day(A1)
C1 : Month(A1)
D1 : Year(A1)
E1 : A1-Int(A1)

2. Copy your value in different cell.
For the day, use the format dd
For the month, use the format mmmm
For the year, use the format yyyy
For the time, use the format h:mm:ss

If Excel doesn't detect your value as a date. You can use the text to columns tool inside Excel and put a space as your separator.

 

[MOCCOM]

Thanks Roxxien. I'm actually looking for formula-based solutions, because the data will be added to each week, and this is the ideal solution for the user(s). Excel does not detect these values as dates - so I'm looking for text string extraction formulas.

 

[Roxxien]

Ok, if you need a text string formulas, I create those for you.

I will just need to search a bit because I normally use macro to do that but I know that there is an Excel formula for this.

I will come back as soon as I have it.

 

[Roxxien]

A1 : where your data is
B1 : =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))
C1 : =MID(A1,1,FIND(" ",A1)-1)
D1 : =MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,FIND(",",A1)-FIND(" ",A1,FIND(" ",A1)+1)-1)
E1 : =MID(A1,FIND(",",A1)+2,LEN(A1)-FIND(",",A1))

Those formula should solve your problem perfectly.

However, I'm sure there is a simpler solution, but I don't know it.

 

[MOCCOM]

Super! Amazing... I was really struggling with these. Thank you. Is there an easy modification to the Day formula so I can just return the number - without the st, nd and rd at the end e.g. 21(st)?

 

[Roxxien]

not sure if it will always work but this should do the trick, I think

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-2)