Excel DATEVALUE Function

DATEVALUE converts a date that's stored as text into a serial number Excel recognizes as a real date. The function only accepts text as input. Feed it a cell that already contains a genuine Excel date and it throws #VALUE!, which trips up more people than the missing-year behavior ever does.

Available in: all Excel versions since Excel 2007. DATEVALUE itself works the same everywhere. The array spill behavior described below, feeding it a full range and getting back multiple results without confirming an array formula, is available in Excel 365 and Excel 2021 only. In Excel 2019 and earlier, entering DATEVALUE against a range requires Ctrl+Shift+Enter as a legacy array formula, or copying the formula down row by row.

Syntax

=DATEVALUE(date_text)

Parameter	Required	Description
date_text	Yes	The date written as text, either typed directly in quotes or referenced from a cell. Excel must be able to recognize it as a date format based on your system's regional settings.

Basic Example

You imported an order log from a CSV export, and the Order Date column landed as text, left-aligned and unusable in any date math.

=DATEVALUE(A2)

// A2 = the text string "07/15/2024" imported from the CSV file

Excel reads the text, matches it against a recognized date pattern, and returns 45488, the serial number for July 15, 2024. That number is what you'll actually see in the cell until you apply a date format. Once formatted, the cell displays 7/15/2024 and you can subtract it from another date, sort it chronologically, or drop it into a PivotTable date grouping, none of which work on raw text.

How DATEVALUE Works

It returns a serial number, not a formatted date

The output of DATEVALUE is always a plain number, counted from Excel's day-zero of January 1, 1900. Apply a date format (Ctrl+1 > Number > Date, or Format Cells) to make it display as an actual date. Skip this step and you'll be staring at 45488 wondering why the formula "isn't working."

It fills in gaps when day or year information is missing

If your text omits a day, Excel assumes the 1st. If it omits a year, Excel pulls the current year from your system clock. =DATEVALUE("March 2024") returns March 1, 2024. =DATEVALUE("15-Mar") returns March 15 of whatever year your system clock currently reports, since no year was specified in the string.

It requires text, not an actual date value

DATEVALUE fails on a cell that already holds a real Excel date, even though the cell might look identical to one that holds text. This is the single most common source of confusion with this function. Check the alignment: text is left-aligned by default, real dates are right-aligned.

It follows your system's regional date settings

DATEVALUE parses ambiguous strings using whatever date order your computer expects. =DATEVALUE("3/4/2024") returns March 4 on a US-locale machine and April 3 on a UK-locale machine. Same formula, same string, two different answers, and no error to flag the discrepancy.

It can process an entire range at once

On Excel 365, DATEVALUE handles array input directly. Feed it a range instead of a single cell and it spills a column of serial numbers, no need to copy the formula down row by row.

Common Use Cases

Converting dates pulled from a PDF, TXT, or database export

Text exports rarely preserve real date formatting. DATEVALUE is the standard fix once the text lands in Excel.

=DATEVALUE(B2)   // B2 holds "2024-01-09" imported from a text file

Sorting or filtering a column that's stuck as text

A column of text dates sorts alphabetically, not chronologically, which puts "1/2/2024" before "12/1/2023." Converting with DATEVALUE fixes the sort order and lets AutoFilter's date grouping work correctly.

=DATEVALUE(C2)   // returns a real date Excel can sort and filter properly

Standardizing a column that mixes real dates with text dates

Imports often produce a column where some rows are genuine dates and others are text, usually because a few rows were typed manually. Test with ISNUMBER first, since a real date is a number underneath and DATEVALUE will error on it.

=IF(ISNUMBER(D2), D2, DATEVALUE(D2))

// D2 = the mixed value, either a real date or a text date
// ISNUMBER(D2) checks if D2 is already a true date

Splitting a date and time out of one text string

Some exports combine date and time into a single string, like "07/15/2024 14:30." DATEVALUE alone drops the time entirely, so if you need to keep it, parse the date portion with LEFT and DATEVALUE, then recover the time with TIMEVALUE, and add the two results together.

=DATEVALUE(LEFT(E2, 10)) + TIMEVALUE(MID(E2, 12, 5))

// LEFT(E2, 10) = the date portion, "07/15/2024"
// MID(E2, 12, 5) = the time portion, "14:30"

Handling Errors

DATEVALUE returns #VALUE! when it can't parse the input as a date, and this is a catchable error.

Common causes of #VALUE!:

• The text doesn't match a date format your system recognizes
• The referenced cell already contains a real date, not text (DATEVALUE only accepts text input)
• The date falls before January 1, 1900, Excel's earliest supported date
• The string has extra characters, like a trailing space or a stray letter, that break the pattern match

=IFERROR(DATEVALUE(A2), "Invalid date text")

Wrap the formula in IFERROR when you're processing a column of imports and expect some rows to fail cleanly rather than halt a downstream calculation.

Notes & Gotchas

Why does DATEVALUE return a #VALUE! error?

The most frequent cause is feeding it a cell that already holds a real date rather than text. DATEVALUE only accepts text, so a genuine date value, even one that looks identical on screen, gets rejected. The second most common cause is a date earlier than January 1, 1900, since that's Excel's floor for its serial date system. =DATEVALUE("12/31/1899") returns #VALUE! for exactly this reason.

Why does my worksheet show dates that look right but are actually text?

Text dates are usually left-aligned in the cell, while real Excel dates are right-aligned by default. This happens most often after pasting from a CSV, a web table, or a PDF export, where the source data never carried real date formatting. Run =ISNUMBER(A2) on a suspect cell; it returns FALSE for text and TRUE for a genuine date.

How do you use DATEVALUE in Excel?

Point it at a cell or a quoted string containing a date written as text, then apply date formatting to the result. =DATEVALUE(A2) converts the text in A2 to a serial number, and Ctrl+1 lets you format that number to display as a readable date. The two steps, convert then format, are both required; skipping the second leaves you looking at a raw number like 45488.

What is DATEVALUE used for when importing data from PDF, TXT, or CSV files?

It's the standard conversion step for turning text-based export dates into dates Excel can calculate with. Data pulled from PDFs, tab-delimited text files, or CSV exports almost always lands as text, since those formats have no concept of a native Excel date type. Wrapping the imported column in DATEVALUE, then formatting the result, is usually the first cleanup step before any date math or PivotTable grouping.

Does DATEVALUE work on a string that includes both a date and a time?

Yes. DATEVALUE ignores the time portion completely and returns the serial number for the date alone, whether the string is "07/15/2024 14:30", ISO format with a T separator, or one that includes seconds and AM/PM. It doesn't throw an error because of the time component. #VALUE! still happens if the date portion itself is unparseable, for the same regional-settings and formatting reasons covered above, not because a time was present. If you need to keep the time, DATEVALUE alone won't do it: split the string with LEFT and TIMEVALUE, then add the two results together.

Does DATEVALUE change results based on regional settings?

Yes, and this is the gotcha most articles skip. DATEVALUE parses ambiguous formats like "03/04/2024" according to your system's locale, so the same formula returns March 4 on a US machine and April 3 on a UK machine, with no warning either way. Unambiguous formats, spelled-out months ("15 March 2024") or ISO order ("2024-03-15"), avoid the problem entirely.

Does DATEVALUE work with a range of cells instead of one?

Yes, in Excel 365 it spills results across multiple cells when given a range as input. =DATEVALUE(A2:A100) returns 99 converted values without needing to be copied down row by row. Older Excel versions require entering the formula once per row or using a legacy array formula with Ctrl+Shift+Enter.

Related Functions

Function	Use this when...
DATE	You're building a date from separate year, month, and day numbers instead of parsing text.
TEXT	You need to go the other direction, converting a real date into a formatted text string.