Excel DATEVALUE & TIMEVALUE — Rescue Dates and Times Trapped as Text

TL;DR — Excel stores every date as a serial number (days counted from 1 Jan 1900); the date you see is that number wearing a date format. When a date arrives as text, it isn't a serial — so it won't sort chronologically, subtract, or feed date functions. DATEVALUE parses a text date into its serial: =DATEVALUE("2025-01-01") → 45658 (format the cell as a date to see it). TIMEVALUE does the same for a time, returning a fraction of a day: =TIMEVALUE("2:30 PM") → 0.6041…. The one trap to respect: for ambiguous strings like "03/04/2026", DATEVALUE follows your regional settings, so it may read March 4 or April 3.

=DATEVALUE("2025-01-01")           ' -> 45658   (serial; format as date to see 2025-01-01)
=TIMEVALUE("2:30 PM")              ' -> 0.604167 (fraction of a day)
=DATEVALUE("2-Jul-2026") + TIMEVALUE("14:30")   ' a real date-time serial

The situation is always the same: you get a column of dates from an export, they line up on the left of the cells, and nothing date-shaped works — they sort "1/12" before "2/03" alphabetically, =B2-A2 throws #VALUE!, and DATEDIF refuses. They look like dates but they're text. DATEVALUE and TIMEVALUE turn the impostors back into real serials. This guide covers the mental model, the regional trap that catches everyone, and the cases where a different tool is the only thing that'll parse the string.

What you'll learn

The mental model: a date is a serial number; a text date is an impostor
How to recognise text dates (and why they break sorting and math)
DATEVALUE and the regional ambiguity trap with 03/04/2026
TIMEVALUE, fractions of a day, and combining date + time
When DATEVALUE can't parse a string — and what to use instead
The judgment call: DATEVALUE vs DATE(LEFT,MID,RIGHT) vs Text to Columns

The mental model: a date is a number in a costume

Type 2025-01-01 into a cell and Excel stores 45658 — the number of days since its epoch — then dresses it up with a date format. That's why date arithmetic works: end - start is just subtraction of two day-counts, and EOMONTH, NETWORKDAYS, and DATEDIF all operate on the serial underneath.

A text date skips all of that. "2025-01-01" stored as text is the string of characters 2 0 2 5 - 0 1 - 0 1 — there is no 45658 underneath, so none of the machinery engages:

' A2 holds the TEXT "2025-01-01"
=A2 + 1        ' -> #VALUE!   (you can't add 1 to a string)
=DATEVALUE(A2) ' -> 45658     (now it's a real serial; =+1 gives the next day)

Hold the picture: DATEVALUE doesn't change how the date looks — it gives the lookalike a real number underneath. After it, apply a date format so the 45658 shows as 2025-01-01 again.

How to spot a text date

The same tells as text-numbers, plus a couple specific to dates:

Values are left-aligned (real dates align right).
Sorting is alphabetical, not chronological — "03/…" lands before "11/…" regardless of year.
=laterdate - earlierdate returns #VALUE! instead of a day count.
DATEDIF, EOMONTH, NETWORKDAYS, and date filters either error or treat each string as an unrelated label.

If date math is misbehaving, assume text dates until proven otherwise.

DATEVALUE and the regional-ambiguity trap

DATEVALUE reads any string Excel recognises as a date in the current regional settings. That last clause is the whole trap. Consider "03/04/2026":

=DATEVALUE("03/04/2026")   ' US settings  -> March 4, 2026
=DATEVALUE("03/04/2026")   ' UK settings  -> April 3, 2026

Same string, different day, and no error to warn you — the workbook just quietly means something different on another machine. Two defences:

Prefer unambiguous input: ISO "2026-07-02" and spelled months "2-Jul-2026" parse the same everywhere.
When you control the parse, don't rely on DATEVALUE's guess — decompose the string with DATE(year, month, day) so you decide which piece is the month (see below).

DATEVALUE also ignores any time portion in the string (use TIMEVALUE for that), and returns #VALUE! for text it can't read as a date at all.

TIMEVALUE and fractions of a day

Excel stores a time as the fraction of a 24-hour day: midnight is 0, noon is 0.5, 6 PM is 0.75. TIMEVALUE parses a text time into that fraction:

=TIMEVALUE("2:30 PM")     ' -> 0.604167   (14.5 / 24)
=TIMEVALUE("23:15")       ' -> 0.968750

Because a real date-time is serial + fraction, you rebuild one by adding the two parsers together:

' A2 holds the text "2026-07-02 14:30"
=DATEVALUE(LEFT(A2, 10)) + TIMEVALUE(MID(A2, 12, 5))   ' -> a true date-time serial

Format the result as yyyy-mm-dd hh:mm and you have a value you can subtract, sort, and compare like any other timestamp.

When DATEVALUE can't do it

DATEVALUE only parses formats Excel already recognises as dates. It fails on genuinely non-standard strings — "20260702" (no separators), "July2026" (no day), "Q3 2026" — and it needlessly errors if the value is already a real date (it's a number, not text, so there's nothing to parse). For those, reach for structural parsing instead:

' "20260702" -> pull the pieces out by position and rebuild the date
=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))   ' -> 2026-07-02, no ambiguity

DATE(year, month, day) takes three numbers and builds a serial with zero guesswork about which part is the month — which is exactly why it beats DATEVALUE whenever the format is fixed but odd. See LEFT / RIGHT / MID for the extraction side.

The judgment call

Standard, unambiguous text date (ISO or spelled month) → DATEVALUE is clean and live. Good for a formula that must re-parse changing text.
Ambiguous dd/mm vs mm/dd → don't trust DATEVALUE's locale guess; decompose with DATE(LEFT,MID,RIGHT) so you control the order.
A one-off column to clean → Data → Text to Columns → Finish, choosing the Date column format (DMY/MDY), converts in place with no formula.
Recurring imports → fix the type at the source or in Power Query; don't ship a DATEVALUE column you rebuild every week.

Once your dates are genuine serials, the whole date toolkit opens up — day counts, EOMONTH for month-ends, NETWORKDAYS for working days, DATEDIF for ages and tenures. DATEVALUE is simply the turnstile that gets text dates into that world.

How ExcelMaster helps

Text dates are uniquely nasty because they look correct — the bug hides until a total or a sort quietly goes wrong. ExcelMaster recognises the pattern (dates that won't subtract, sorts that come out alphabetical), and — crucially — handles the ambiguity for you: it reads whether a column is dd/mm or mm/dd from the data itself, then converts with the right parse, DATEVALUE for standard strings or a DATE(...) decomposition for the weird ones. You get real serials you can do math on, without betting your quarter-end on which country's date convention Excel assumed.

Frequently asked questions

How do I convert text to a date in Excel?

=DATEVALUE(A1), then format the cell as a date. For a whole column with no formula, use Data → Text to Columns → Finish and pick the Date format. For non-standard strings, rebuild with =DATE(LEFT,MID,RIGHT).

Why does DATEVALUE return #VALUE!?

The string isn't a format Excel recognises as a date in your region — e.g. "20260702" with no separators, a missing day, or the value is already a real date (a number, so there's nothing to convert). Use DATE(...) decomposition for odd formats.

Why are my dates sorting wrong?

They're text, not real dates, so they sort alphabetically ("03/…" before "11/…"). Convert them with DATEVALUE or Text to Columns so Excel sorts by the underlying serial number.

What does TIMEVALUE return?

A fraction of a day: =TIMEVALUE("2:30 PM") → 0.6042 (14.5 ÷ 24). Add it to a DATEVALUE result to build a full date-time serial.

DATEVALUE vs DATE — which should I use?

DATEVALUE parses a text string and guesses the format from your locale. DATE(year, month, day) builds a date from three numbers with no ambiguity. Use DATE(...) (with LEFT/MID/RIGHT) whenever the text order is fixed but unusual, or when dd/mm vs mm/dd could be misread.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-02.