DATEVALUE Function

Turns a date written as text into a real date.

Category

Date & Time

Available in

All versions, Microsoft 365

Returns

A date (serial number)

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Dates & Times module

Overview

Dates pasted from other systems often arrive as text — they sit on the left of the cell and won't sort or calculate. DATEVALUE reads that text and returns the date's serial number.

It understands the forms Excel recognises when you type: 3/15/2026, 2026-03-15, Mar 15, 2026 and 15-Mar-2026 (with US settings). Any time in the text is ignored.

Syntax

=DATEVALUE(date_text) Explain

Arguments

ArgumentRequired?What it does
date_textYesText that looks like a date, or a cell holding it.

Examples

Example 1 — Different text forms, same date

Every one of these returns 46096 — the serial number for March 15, 2026.

B2fx=DATEVALUE("3/15/2026")
AB
1TextDATEVALUE
2"3/15/2026"46096
3"15-Mar-2026"46096
4"March 15, 2026"46096
5"2026-03-15"46096

Format the results as Short Date to see 3/15/2026.

Example 2 — Building a date from text pieces

A report lists the day, month name and year separately. Join them into text DATEVALUE can read.

D2fx=DATEVALUE(B2&" "&A2&", "&C2)
ABCD
1DayMonthYearDate
215Mar20263/15/2026
31September20269/1/2026
=DATEVALUE(B2&" "&A2&", "&C2) Explain

Common mistakes

  • #VALUE! on a cell that already holds a date. DATEVALUE only accepts text. If the cell is right-aligned it's already a real date — use it directly.
  • Day-first dates like 15/03/2026. With US settings Excel reads the first number as the month, so 15/03/2026 fails. Rebuild it with DATE, LEFT, MID and RIGHT, or use Data › Text to Columns › Date: DMY.

Notes

  • To convert a whole column at once, Data › Text to Columns (Finish straight away) often fixes text dates without any formula.
  • Related functions: DATE, TIMEVALUE, VALUE, TEXT.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.