Spreadsheets

Convert 05-Jan-24 style text to real dates in Excel

Convert 05-Jan-24 style text to real dates in Excel: when DATEVALUE and Text to Columns just work, and a MATCH fallback when they do not.

Editorial illustration for Convert 05-Jan-24 style text to real dates in Excel

Dates written with a month name, such as 05-Jan-24, are the easy case: because Jan cannot be mistaken for a day, Excel usually converts them with Text to Columns or DATEVALUE regardless of day-month order. When that fails, build the date with DATE and MATCH on a list of month names.

Why month-name dates behave better

With 05/01/2024, Excel has to guess which number is the month, and your regional setting decides. With 05-Jan-24 there is no guessing: the middle part is plainly a month. So the day-and-month swap that damages numeric dates is not a danger here. The things that can still go wrong are the two-digit year, hidden spaces and a non-English system language.

Try the quick routes first

  • Select the column and use Data, Text to Columns, Next, Next, then Date with any order selected, and Finish. Excel recognises the month abbreviation.
  • In a spare column, enter =DATEVALUE(A2) and fill down. Format the results as dd-mmm-yyyy.
  • Or simply add zero: =A2+0. Excel coerces a recognisable date string into its serial number, for text such as 05-Jan-24.

Check the result with =ISNUMBER(B2). If the answer is TRUE for every row, you are done. Say 05-Jan-24 becomes 45296 and 28-Feb-24 becomes 45350, then the serial numbers are right: 1 January 2024 is 45292, and 28 February is 31 plus 27 days later, which is 45350.

When do the quick routes fail?

  • A non-English Windows language, where Jan, Mar or May is read as something else.
  • Month spellings such as Sept that the parser may not accept.
  • Hidden spaces or non-breaking spaces from a PDF conversion. Clean the text first with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
  • Text that includes a weekday or a time as well.

In those cases, the safe fallback is to look the month up yourself.

The fallback: DATE with MATCH

Assume text such as 05-Jan-24 in A2, with a fixed width of nine characters. The day is the first two, the month name is characters four to six and the year is the last two. This formula builds the date, adding 2000 to the year:

  • =DATE(2000+RIGHT(A2,2),MATCH(MID(A2,4,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),LEFT(A2,2))

MATCH returns the position of the month in the list, which is the month number: Jan is 1, Dec is 12, and the final 0 means exact match. If your date has a four-digit year, as in 05-Jan-2024, replace the year part with RIGHT(A2,4) and drop the 2000 plus.

If you would rather keep the month names visible, type them in cells H2 to H13 and use MATCH(MID(A2,4,3),$H$2:$H$13,0) instead of the braces. That is easier to correct if a statement writes Sept, since you can add the spelling. Where the braces behave oddly in Google Sheets because of a locale's separators, the cell-list version avoids the problem.

What about the two-digit year?

Adding 2000 yourself decides the century. DATEVALUE and Text to Columns use Excel's own rule instead, which treats years up to 29 as 20xx and 30 to 99 as 19xx. That is fine for 24, risky for a long-dated deposit maturing in 31, and the reason the formula route is clearer. A later article covers the century rule in more detail.

Variants are worth knowing. For 05-Jan-2024, change RIGHT(A2,2) to RIGHT(A2,4) and drop the 2000 plus. For text with a weekday in front, such as Fri 05-Jan-24, shift every position by four characters or strip the first four with MID(A2,5,20) before you parse.

Final checks

  1. Format the column dd-mmm-yyyy so you can compare directly with the statement text.
  2. Use =COUNT(B2:B500) and compare with the number of rows.
  3. Sort and check that the first and last dates match the statement period.

Month-name dates are friendly, but the same cleanup habits still apply: keep the original text column until you have verified the dates, then paste values over it.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports