Forum Discussion
text to date formatting issue
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, inclu...
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?
- 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).
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.
