Parsing date format of mmm dd yyyy to excel date format

Question

We use a booking system at work that exports reports where the date is entered in the format mmm dd yyyy (for example "Jun 01 2018"). Excel does not recognise this and the usual date formatting formulas can't touch it.

Is there a way to parse this using a formula so I can simply dump this export into a spreadsheet so I can perform quick analysis on booking behaviour.

I have not been able to find a solution to this online for this specific issue in Excel.

Thanks.

Answer 1

I hadn't thought of @Jeeped's method, using Text-to-Columns. I like it; that's probably the quickest way if you have a list of dates that need to be converted ad-hoc.

If you need a worksheet formula to do the conversion on an "ongoing" basis, this formula will also do the trick:

=DATEVALUE(SUBSTITUTE(A1," ",", ",2))

...where the string containing the date in MMM DD YYYY format is in cell A1.

Basically, Excel is picky about a comma.

Excel doesn't recognize Aug 08 2018 as a date, but it does recognize Aug 08, 2018 as a date.

This formula replaces the second space with a comma+space, and then DATEVALUE will recognize it as a date.

As usual, the date will be returned as a serial number, so then you can format the cell as a date with the Format Cells dialog: Ctrl+1 or (Right Click) > Format Cells.

---

Further to @Jeeped's method in his comment, this is how to do the same thing using Text-to-Columns:

1. Select (highlight) the cells to "convert"
2. on the Data tab of the ribbon, click Text to Columns
3. Choose Fixed Width (if not already selected), then click Next
4. Double-click each break line (if there are any), then click Next
5. Choose Date and then in the drop-down, choose MDY
6. Click Finish

---

More Information:

• Office Support : DATEVALUE function
• Office Support : SUBSTITUTE function
• Office Support : Split text into different columns with the Text to Columns Wizard
• ExcelTrainer : Converting Date Format Using Text To Columns