# How to fix dates stored as text in Excel

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

**Excel:** `=DATEVALUE(A2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Imported date | Real date |
| 2026-03-02 |  |
| 2026-03-09 |  |
| Mar 16, 2026 |  |
| 2026-03-23 |  |
| 3/30/2026 |  |
| April 6, 2026 |  |

Formula in B2, filled to B7: `=DATEVALUE(A2)`

Result: 3/2/2026; 3/9/2026; 3/16/2026; 3/23/2026; 3/30/2026; 4/6/2026

## 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.

## Other ways

- `=--A2`: Any arithmetic converts date text the same way: --A2, A2*1 or A2+0.
- `=DATEVALUE(TRIM(A2))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/fix-dates-stored-as-text