text to date formatting issue

Copper Contributor

Hi - I have a field in a report that has date/time information however, I'm having problems with converting this field to a date formatting. I have tried every trick in the book to get it down, including text-to-column. 

 

If anybody could assist - it'd be greatly appreciated. Data example attached. 

4 Replies

@ncstle If it's just the date date you need, use text-to-columns to separate the date from the time stamp using space as the delimiter. Then select "Date: MDY" as the data type for the date column and skip the time column. You'll end up with a column containing real dates.

Alternatively, use Power Query.

I do need both the date and time. Do you have have any suggestions to resolve this that includes both?

@ncstle Actually, am playing around with your table a bit. Try Find & Replace. Find AM and PM and replace with nothing (blank). Fair chance that your Excel will immediately transform the cells to real date/time cells.

 

If not, I'd go for Power Query. Especially if you need to do this very frequently and on large(r) data sets.

 

I don't think your data is in a proper time format as there is no space between the time and the AM/PM. Try doing a find/replace (ctrl+h) on your actual delivery column and add a space by replacing "AM" with " AM" and "PM" with " PM" (without quotes - I used the quotes so you can seep where the space is).