Date Format Help

[Sully1440]

Hi All,
This is probably a simple fix, but I'm stuck....

I have a date & time one column, that I'd like separated into 1.) Date (example: March 14, 2001) and 2.) Time
I just can't seem to get this working. Any help would be appreciated.

Thx,
Jim

See attached

[georgiboy]

For date maybe:

=Text(A1,"dd/mm/yyyy")

Time:

=Text(A1,"hh:mm")

Hope this helps

[Sully1440]

It doesn't work. It just copies the exact same thing to the new columns. I'm trying to get date in one column and time in the other. The other problem is that the date 01/05/2018 isn't recognized as a date.

[georgiboy]

Try this for date then:

=TEXT(B2,"mmm dd, yyyy")

This for time:

=Text(B2,"hh:mm")

If you want a date value then maybe:

=TEXT(DATEVALUE(B2),"mm/dd/yyyy")
=DATEVALUE(TEXT(B2,"mm/dd/yyyy"))

Then format the cell to how you want a date to look

[Paul_Hossler]

It looks like the strings in Col A are

DD/MM/YYYY space HH:MM:SS AM

????

If so then you can try

=TEXT(DATE(MID($B2,7,4), MID($B2,4,2), MID($B2,1,2)),"mmmm dd, yyyy")

=TEXT(TIMEVALUE(RIGHT($B2,LEN($B2)-FIND(" ",$B2))),"hh:mm:ss AM/PM")

[Sully1440]

Thank you Paul. It works perfectly. :)

Jim