ExcelDateIntermediate

DATEVALUE

Converts a date represented as text to an Excel serial number.

Read the syntaxReview worked examplesOpen the spreadsheet app
=DATEVALUE(date_text)
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Converts a date represented as text to an Excel serial number.

The DATEVALUE function converts a date stored as text into an Excel serial number, which is how Excel internally stores dates. This is particularly useful when importing data from other systems or when dates are entered as text strings that Excel doesn't automatically recognize as dates. The `ext` argument must be a text string that represents a date in a format Excel recognizes (e.g., "1/1/2023", "January 1, 2023"). If the year is omitted, DATEVALUE uses the current year. It's important to note that DATEVALUE does not include time information; if `ext` includes a time, it is ignored. The result can then be formatted as a date using number formatting.

Quick reference

Syntax

=DATEVALUE(date_text)

Inputs

Arguments

date_textA text string representing a date in an Excel-recognized format.
Example: "1/1/2023"

Formula patterns

Examples

1

Convert "1/1/2023" to a serial number

=DATEVALUE("1/1/2023")
text to datedata cleaningdate conversion
2

Convert date text from a cell

=DATEVALUE(A2)
cell referencesimported data

Avoid these issues

Common Errors

1

#VALUE!

Cause: Occurs if `date_text` is not a valid date string that Excel can recognize.

Fix: Ensure the text string is in a format Excel can interpret as a date (e.g., "MM/DD/YYYY", "DD-MMM-YY").

2

Incorrect Year

Cause: If `date_text` omits the year, DATEVALUE uses the current year, which might not be the intended year.

Fix: Always include the year in the `date_text` argument to avoid ambiguity, especially when working with historical data.

Platform support

Compatibility

Excel 2007+Google Sheets

Available in Excel 2007 and later versions, including Excel 365.

Source: Microsoft Support

Common questions

Frequently Asked Questions

Converts a date represented as text to an Excel serial number.

ext: A text string representing a date in an Excel-recognized format.

#VALUE!: Ensure the text string is in a format Excel can interpret as a date (e.g., "MM/DD/YYYY", "DD-MMM-YY"). Incorrect Year: Always include the year in the `ext` argument to avoid ambiguity, especially when working with historical data.