Spreadsheet Practice

How to fix dates stored as text in Excel

Formula (Excel and Google Sheets)

=DATEVALUE(A2)

Convert them with DATEVALUE and format the result as a date: =DATEVALUE(A2).

Result on the example
one result per row in B2, filled down to B7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

How it works

Imported dates often arrive as text: they sit on the left of the cell, ignore date formats and sort alphabetically. DATEVALUE reads the text the way it would be read if you typed it into a cell and returns the date's serial number, which the date format then displays.

=DATEVALUE(A2)

Result3/2/2026 (46083)

  1. A2"2026-03-02"

    The value in A2.

  2. DATEVALUE(A2)3/2/2026 (46083)

    DATEVALUE: Converts date text to a date.

    • date_textA2"2026-03-02"

In Google Sheets

The same formula works in Google Sheets: =DATEVALUE(A2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=--A23/2/2026, 3/9/2026, 3/16/2026…Any arithmetic converts date text the same way: --A2, A2*1 or A2+0.
=DATEVALUE(TRIM(A2))3/2/2026, 3/9/2026, 3/16/2026…Trim first when imports leave spaces around the dates.

Common mistakes

  • DATEVALUE on a cell that already holds a real date returns #VALUE!. Mixed columns need a check first.
  • Text in day/month/year order is read using your computer's settings and can swap day and month. Rebuild it with DATE, RIGHT, MID and LEFT.
  • Without a date format the result shows as a number like 46083.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Fix dates stored as text

In B2, turn the imported date text into a real date. Fill down to B7.

Questions

How do I check whether a date is text?

=ISTEXT(A2) is TRUE for text; =ISNUMBER(A2) is TRUE for a real date.

How do I fix the column in place?

Select it and use Data, Text to Columns, Finish. Recognizable dates are converted as it goes.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.