Date and Time in Same Column as Text Question

[pick44]

I have database where the date and time are showing as text in same column. Need to separate them into columns showing date and time. See attached. Thanks pick ><>

[Bo_Ry]

Please try

=--(20&MID(A1,5,2)&TEXT(LEFT(A1,4),"-00-00"))

and
=--TEXT(RIGHT(A1,4),"00\:00")

[MrShorty]

You will need to use text manipulation functions to extract the different date/time elements and put them back together using the DATE() and TIME() functions. For the Date, for example, something like =VALUE(LEFT(A1,2)) will get the month value, VALUE(MID(A1,3,2)) will get the day of the month value, and VALUE(MID(A1,5,2)) will get the two digit year (assuming these are 21st century dates, you could add 2000 to the result to get the unambiguous 4 digit year). Put those into the DATE() function to get the correct date serial number =DATE(2000+VALUE(MID(A1,5,2)),LEFT(A1,2),MID(A1,3,2)). The formula for the time of day would be similar.

[pick44]

Thank you. The formula worked.