DATE vs DATEVALUE in Excel

The short answer to DATE vs DATEVALUE: DATE builds a date from three numbers (year, month and day), and DATEVALUE turns a piece of text that looks like a date into a real date. Both return the same kind of result, a date serial number. This comparison is for anyone who imports data with dates stored as text, or builds dates from separate columns, and wants the reliable option.

DATE vs DATEVALUE at a glance#

DATE works from numbers you already have; DATEVALUE works from text you need to convert.

What a "real date" is in Excel#

A real date in Excel is a number. Excel counts days from 1 January 1900, which is day 1, so 15 March 2024 is stored as 45366. The cell's number format decides whether you see 45366 or 15/03/2024. This is why you can subtract one date from another to count days.

Text that looks like a date is different. It is just characters, so you can't do date maths with it, sort it properly or group it in a pivot table. You can usually spot text dates because they align to the left of the cell, while real dates align to the right. Both DATE and DATEVALUE exist to give you a real date. The date basics lesson shows how serial numbers and date formats work together.

How DATE works#

DATE takes the year, month and day as separate numbers and returns the matching date.

=DATE(2024,3,15)

This returns 15 March 2024. The arguments can be cell references or other formulas, so DATE is the tool for building dates from parts. If B2 holds 2024, C2 holds 3 and D2 holds 15, =DATE(B2,C2,D2) gives the same date.

DATE also rolls over values that are too big. =DATE(2024,14,1) returns 1 February 2025, and =DATE(2024,2,30) returns 1 March 2024, because February 2024 has 29 days. This is a feature, not a bug: =DATE(YEAR(A2),MONTH(A2)+1,1) gives the first day of the next month even in December. See how to add days, months and years for more uses.

How DATEVALUE works#

DATEVALUE takes text and returns the date it describes.

=DATEVALUE("2024-03-15")
=DATEVALUE(A2)

If A2 holds the text 15 March 2024, the second formula returns 45366. Format the cell as a date to see it properly. DATEVALUE ignores any time in the text, so "2024-03-15 14:30" also returns 45366. If the text has no year, such as "15 March", Excel uses the current year.

The catch is that DATEVALUE reads the text using your computer's regional date settings. On a computer set to US format, "03/04/2024" means 4 March. On a computer set to UK format, it means 3 April. A file can give different answers on different computers, with no error to warn you.

The VALUE function converts date text in the same way, and also handles number text such as "1,250". The VALUE function guide explains it, and the text and value lesson shows when text needs converting at all.

The same text dates solved both ways#

Here are dates that arrived as text from an export:

Row 2: ISO text (yyyy-mm-dd)#

=DATEVALUE(A2)
=DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))

Both return 15 March 2024. The year-month-day pattern with dashes is recognised in most settings, so DATEVALUE is fine here.

Row 3: compact text (yyyymmdd)#

=DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2))

This returns 2 September 2024. DATEVALUE returns #VALUE! on 20240902 because there are no separators to tell it where the parts are. DATE with LEFT, MID and RIGHT works because you tell Excel exactly which characters are the year, month and day.

Row 4: day-first text (dd/mm/yyyy)#

=DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2))

This returns 20 January 2025 on any computer. DATEVALUE works only if the computer uses day-first settings. On a US-format computer it returns #VALUE!, because there is no month 20. Worse, a date like 03/04/2025 would quietly become the wrong date.

When to use DATE#

Use DATE whenever you have, or can extract, the year, month and day. It is the better choice when:

• Your data has separate year, month and day columns, which is common in exports.
• The text follows a fixed pattern, such as yyyymmdd, so LEFT, MID and RIGHT can pull out the parts.
• The file will be opened in different countries. DATE never depends on regional settings.
• You calculate dates, such as month starts, quarter ends or a due date 45 days out.
• You type a date inside a formula, such as ">="&DATE(2024,1,1) in SUMIFS, instead of date text that could be misread.

When to use DATEVALUE#

Use DATEVALUE for a quick conversion of clean date text that your settings recognise. It is reasonable when:

• The text is in an unambiguous format, such as 2024-03-15 or 15 March 2024.
• You only open the file on computers with the same date settings.
• You need to strip a time part from text like 2024-03-15 09:00.

For a whole column, Excel's Data > Text to Columns wizard can also convert text dates in place: on the last step, choose Date and pick the order (for example DMY). This is often faster than a formula. Our guide on converting text to dates walks through every method, and CSV vs XLSX explains why text dates appear in the first place.

Common mistakes with DATE and DATEVALUE#

1. Using DATEVALUE on a real date. If the cell already holds a date, DATEVALUE returns #VALUE!. Check the alignment first.
2. Trusting DATEVALUE on dd/mm or mm/dd text. Short slash dates are read according to your settings, so the day and month can swap silently.
3. Putting the arguments in the wrong order. DATE is always year, month, day: =DATE(15,3,2024) returns 13 September 1920, not 15 March 2024.
4. Using a two-digit year. =DATE(24,3,15) returns 15 March 1924, because Excel adds years 0 to 1899 to 1900. Always use four digits.
5. Forgetting to format the result. A result like 45366 is correct; it just needs a date format.
6. Leaving spaces in the text. A leading space can make DATEVALUE fail. Wrap the text in TRIM: =DATEVALUE(TRIM(A2)).

Once you have real dates, the rest of Excel's date functions work on them, from YEAR and MONTH to finding the quarter from a date.

Our recommendation#

Make DATE your default. It takes numbers, never depends on regional settings and handles any fixed text pattern when you combine it with LEFT, MID and RIGHT. Use DATEVALUE (or VALUE) as a shortcut for clean, unambiguous text such as 2024-03-15, and double-check a few results. If a whole column is text, try Text to Columns before writing formulas.

In short: DATE for reliable dates, DATEVALUE for quick fixes on clean text.

Practise it#

Try the exercise on this page: turn yyyymmdd text into a real date with DATE. Then practise dates in a live spreadsheet and the text and value lesson, where you type each formula and get instant feedback.