Spreadsheets

Convert text dates to real dates in Google Sheets

Turn text dates like 05 Mar 2025 or 05-03-25 into real dates in Google Sheets using DATEVALUE, SPLIT and DATE, and set the locale so they stay correct.

Editorial illustration for Convert text dates to real dates in Google Sheets

To convert text dates to real dates in Google Sheets, try DATEVALUE first: =DATEVALUE(A2) turns text such as "05 Mar 2025" into a date number you can format. When the text uses an unusual layout such as 05-03-25, split it into day, month and year with SPLIT and rebuild it with DATE. Check the spreadsheet locale under File, Settings, because that decides how ambiguous strings are read.

How do I know the dates are really text?

A real date is a number underneath. Test one cell with =ISNUMBER(A2); TRUE means a date, FALSE means text. By default text aligns left and dates align right, and applying Format, Number, Date changes a real date but leaves text exactly as typed. You can also try =ISDATE(A2) as a second check, but ISNUMBER is the one to trust if they disagree.

When does DATEVALUE work?

DATEVALUE reads a text string and returns the date as a number. It copes with strings like 05 Mar 2025 and 5 March 2025, and with slash dates when the locale matches the order. Use it like this:

=DATEVALUE(TRIM(A2))

Format the result as a date. To fill a whole column, put this in the first result cell instead of copying down:

=ARRAYFORMULA(IF(A2:A500="","",IFERROR(DATEVALUE(TRIM(A2:A500)),"check")))

Rows that cannot be read show the word check, so you can find them. Text copied from a PDF statement sometimes contains a non-breaking space that TRIM does not remove. If DATEVALUE fails on a string that looks perfect, wrap the cell as SUBSTITUTE(A2,CHAR(160)," ") first.

What about 05-03-25 and other short forms?

Hyphenated or two-digit-year strings are where the locale guesses wrong. Take control and rebuild the date. For text in the pattern day-month-year with two-digit years, such as 05-03-25:

=DATE(2000+VALUE(INDEX(SPLIT(A2,"-"),1,3)),VALUE(INDEX(SPLIT(A2,"-"),1,2)),VALUE(INDEX(SPLIT(A2,"-"),1,1)))

SPLIT cuts the text at each hyphen into a row of pieces. INDEX picks piece 1 (day), 2 (month) or 3 (year) from that row, and DATE puts them in year, month, day order. The 2000+ turns 25 into 2025; if your file can contain years before 2000, check the century by hand. For slash-separated text, swap "-" for "/" in all three places.

For month names written in full words, such as 5 March 2025, DATEVALUE is simpler than any SPLIT approach.

Text in the cellFormulaReal date
05 Mar 2025=DATEVALUE(A2)5 March 2025
05-03-25DATE with SPLIT, as above5 March 2025
27/03/2025=DATEVALUE(A2) with an India locale27 March 2025

Does the spreadsheet locale matter?

It decides how a string such as 05/03/2025 is read. Open File, then Settings, and look at Locale. With a day-first locale such as India, 05/03/2025 is 5 March; with a month-first one it is 3 May. Changing locale also changes the default date style, decimal marks and currency format for the whole file, so change it before you enter lots of data and check a few existing dates afterwards. If you receive files from someone else, the locale belongs to the file, not to you.

What about times, such as 05/03/2025 14:32?

A date with a time attached converts differently. DATEVALUE drops the time and returns only the date. If you need the time too, use VALUE on the whole string, or TIMEVALUE on the time part. For expense tracking you almost never need the time, so DATEVALUE is the safer choice, and it lets SUMIFS compare against whole dates without any end-of-day surprises.

Making it permanent

  1. Convert in a helper column with one of the formulas above.
  2. Copy the result, then use Paste special, Values only, over the original column.
  3. Format the column as a date.
  4. Delete the helper column and keep a copy of the untouched original.

If statement dates keep arriving in odd layouts, SpendixAI can read bank statements uploaded as PDF, CSV or Excel and let you review each row before saving, so the date problem is caught at import. Always compare against the statement.

SPENDIXAI JOURNAL

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

Explore your reports