Formula Guide

The Excel DATEVALUE Function
explained simply

DATEVALUE converts a date stored as text into a real date value that Excel can calculate with.

ExcelPro · 4 min read · Updated June 2026
Contents
  1. What does DATEVALUE do?
  2. Syntax
  3. 3 examples
  4. Common mistakes
  5. FAQ

What does DATEVALUE do?

DATEVALUE takes text that looks like a date — such as "2026-06-22" or "June 22, 2026" — and converts it into a real, working date value Excel can use in calculations.

This matters because dates imported from other systems often arrive as plain text. Until converted, formulas like subtracting two dates will not work correctly on them.

Syntax

=DATEVALUE(date_text)
ArgumentDescription
date_text requiredA text string that represents a date, in a recognizable format.
💡 The result is a serial number

DATEVALUE actually returns the underlying serial number Excel uses for dates internally — format the cell as a date to see it displayed properly.

Examples

Example 1
Convert a text date
=DATEVALUE("2026-06-22")

Returns the serial number for June 22, 2026 — format the cell as a date to see it displayed as a real date.

Example 2
Convert an imported text date column
=DATEVALUE(A2)

If A2 contains the text "06/22/2026", this returns a real, calculable date value.

Example 3
Use inside a date subtraction
=DATEVALUE(B2)-DATEVALUE(A2)

Converts two text dates first, then finds the number of days between them — necessary if both started as plain text.

Common mistakes

⚠️ Forgetting to format the result as a date

DATEVALUE returns a serial number, which looks like a meaningless number until you apply a date format to the cell.

⚠️ Feeding it an unrecognizable format

DATEVALUE depends on the text resembling a real date in a format Excel understands — completely nonstandard formats will return a #VALUE! error.

FAQ

How do I tell if a date is stored as text or a real date?
Text dates left-align by default; real dates right-align. ISNUMBER(A2) also returns FALSE for text dates and TRUE for real ones.

Practise Excel with real data

ExcelPro has 700+ hands-on Excel exercises across 7 career tracks — free to start, no download needed.

Start practicing free →

Related formulas

DATE VALUE TEXT