change text data into regular date

**stephme55** · 01-27-2017, 07:56 PM

I have Dec 1, 2016 1:45:23 AM PST but it is not being read as a date (and changing format with control 1 doesn't help). How can I fix this?

Thanks

**jeffreybrown** · 01-27-2017, 07:59 PM

What are you actually looking for as the date? Just Dec 1, 2016?

**stephme55** · 01-27-2017, 08:40 PM

I was trying to write 1/1/2016 but I have a bunch of other text as well...that won't turn to dates.

**David A Coop** · 01-27-2017, 08:50 PM

Hi Steph,

This formula will give you the date - assuming the text string is in A1.
=DATE(MID(A1,8,5),MONTH(1&LEFT(A1,3)),MID(A1,4,FIND(",",A1)-4))

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

**jeffreybrown** · 01-27-2017, 09:00 PM

The easiest method could be to use Text to Column with a Fixed Width with the Step 3 of 3, format the first column as a date with MDY

**MrShorty** · 01-27-2017, 11:15 PM

If I enter that string without the PST at the end, my copy of Excel will automatically recognize the date/time. Is the PST a critical part of the data entry?

Along the same lines as jeffreybrown's use of Text to Columns, you could do the same thing, but put the column break between "AM" and "PST", and keep the date/time together as a single entry.