Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All functions

DATEVALUE

Dates

Every export arrives with dates as text. This is how they become dates again.

Difficulty

Amateur

Excel file

1What is it?

A column of dates that won't sort, won't filter by month and won't subtract is text, not dates — and it looks completely normal. DATEVALUE converts a text date to the serial number Excel actually stores, which you then format as a date. The catch worth knowing about is ambiguity: `03/04/2026` is the 3rd of April or the 4th of March depending on the machine's locale, and Excel will silently pick one. A date that's already a real date makes DATEVALUE fail, which is a useful accident — it doubles as a test of what you've got.

2What it looks like

DATEVALUE(date_text)

date_text: Text in a format the machine's locale recognises. An unambiguous form like 2026-04-03 is read the same way everywhere.

3When you use it

Fix an exported date column so it sorts and filters.
Turn a text date into something EOMONTH and NETWORKDAYS can use.
Test whether a column is genuinely dates — if DATEVALUE errors, it already was.

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

Try an unambiguous format, then an ambiguous one, then something that's already a real date.

=DATEVALUE(A2)Serial 46115 — format the cell and it reads 3 April 2026

	A
1	Result
2	Serial 46115 — format the cell and it reads 3 April 2026
3	Unambiguous — read the same way on any machine

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

What the export gave you

5Common errors

#VALUE!The text isn't a recognisable date — or it's ALREADY a real date, which DATEVALUE refuses.

Fix: Both are informative. If it errors on the whole column, the column was fine all along and the problem is elsewhere.

Result is a 5-digit numberThe conversion worked; the cell just isn't formatted as a date.

Fix: Format the cell, or wrap in TEXT for a label.

Every date is wrong by the same patternDay and month were swapped by locale — 03/04 read as March 4th rather than April 3rd.

Fix: Use an unambiguous source format (yyyy-mm-dd), or rebuild with DATE(y, m, d) from the parts.

Two-digit years land in the wrong centuryExcel maps 00-29 to 2000s and 30-99 to 1900s.

Fix: A "30" year means 1930. Four-digit years remove the guesswork.

6Better functions & alternatives

Text to Columns → Finish — Converts a whole column in one pass with no formula at all — usually the faster fix. See the tip.
DATE(year, month, day) — Rebuild from the parts when the text is too irregular for DATEVALUE.

Want DATEVALUE already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.