Why does DATEVALUE give #VALUE! when I paste a date from an email or web page?

Emails and web pages often include extra text, time zones, or non-standard formatting around the date. Extract just the date portion (e.g., '1/15/2020') and ensure it matches your system's date format. Test with a simple, unambiguous date first.

Can DATEVALUE handle dates with times, like '1/15/2020 14:30'?

DATEVALUE extracts only the date part and discards the time. If you need the time component, use TIMEVALUE on the time portion separately, or import the full datetime as a single value using your system's datetime functions.

What's the difference between DATEVALUE and just using DATE()?

DATE(year, month, day) constructs a date from three separate numbers; DATEVALUE parses a single text string into a date. Use DATE when you have year, month, and day in separate columns; use DATEVALUE when the entire date is in one text cell.

How do I convert a whole column of text dates to real dates?

Create a helper column with =DATEVALUE(A2) and copy the formula down, then copy the results and Paste Special > Values to replace the original text with date serial numbers. In Google Sheets, BYROW(A:A, LAMBDA(x, DATEVALUE(x))) converts the entire column in one formula.

DATEVALUE function

DATEVALUE converts text strings representing dates into serial numbers Excel and Sheets recognize, enabling date calculations and sorting.

=DATEVALUE(date_text)

Generate a DATEVALUE formula

Describe what you need. The generator will reach for DATEVALUE where DATEVALUE is the right tool, and tell you when it is not.

Arguments

How DATEVALUE reads its arguments

Argument	Required	Description
date_text	Required	A text string in a recognized date format (e.g., '1/15/2020', '15-Jan-2020'). If the text cannot be interpreted as a valid date, DATEVALUE returns #VALUE!.

Returns

Returns a serial number representing the date.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate days employed since hire date

Employee ID	Name	Hire Date	Days Employed
E001	Sarah Chen	1/15/2020	=TODAY()-DATEVALUE(D2)

=TODAY()-DATEVALUE("1/15/2020")

Result: 2103

DATEVALUE converts the text '1/15/2020' into a serial number (44246). Subtracting from TODAY() (as of Sept 16, 2026) gives 2103 days—Sarah's tenure. This demonstrates how DATEVALUE unlocks date arithmetic on text-formatted dates that would otherwise be treated as plain text.

2. Extract hire year for reporting

Employee ID	Name	Hire Date	Hire Year
E002	Marcus Johnson	3/22/2018	=YEAR(DATEVALUE(C2))

=YEAR(DATEVALUE("3/22/2018"))

Result: 2018

Once DATEVALUE converts the text date to a serial number, YEAR can extract the year component. Marcus was hired in 2018, making this useful for grouping employees by hire cohort or filtering rosters by tenure bands.

3. Compare hire dates to find earliest employee

Employee ID	Name	Hire Date
E001	Sarah Chen	1/15/2020
E002	Marcus Johnson	3/22/2018

=IF(DATEVALUE("1/15/2020")<DATEVALUE("3/22/2018"),"Sarah","Marcus")

Result: "Marcus"

DATEVALUE converts both text dates to serial numbers, enabling numerical comparison. Marcus (serial 43534) predates Sarah (serial 44246), so the IF returns his name. This pattern scales to find the longest-tenured employee or sort an entire roster by hire date.

Common errors

Which DATEVALUE error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The date_text string is not in a recognized date format, such as DATEVALUE("September 999th") or DATEVALUE("45/13/2020").	Use a standard date format: 'M/D/YYYY', 'MM-DD-YYYY', or 'D-Mon-YYYY'. Verify the month (1–12) and day (1–31) are valid numbers within range.
#VALUE!	The date_text is empty or contains only whitespace, as in DATEVALUE("") or DATEVALUE(A1) where A1 is blank.	Wrap DATEVALUE in IFERROR to handle blanks gracefully, or add a conditional check to ensure the source cell contains a non-empty date string before passing it to DATEVALUE.
#VALUE!	The locale or system date format doesn't match the text input, such as passing '2020/15/01' (day-first format) when the system expects 'MM/DD/YYYY' (month-first).	Ensure the date text format matches your system locale setting, or use locale-independent formats like '1-Jan-2020' that work across regions.

Tips and when to use something else

DATEVALUE is essential when importing dates from CSV files, APIs, or external systems where dates arrive as text—it converts them into proper date values your spreadsheet can calculate with.
If you're working with multiple text dates, use DATEVALUE on all of them before comparing or sorting; comparing text dates directly gives alphabetical results, not chronological.
For most use cases, prefer DATE(year, month, day) if you already have year, month, and day in separate columns—it's more predictable across locales than DATEVALUE.
To apply DATEVALUE to an entire column at once, create a helper column with the formula and copy down, then Paste Special > Values to replace text dates with proper serial numbers.

Related functions

TODAYTODAY returns the current date as a date value, automatically updating each time the spreadsheet opens or recalculates.DATEDATE returns a date serial number from year, month, and day arguments, enabling date construction from separate numeric components.DATEDIFReturns the number of time units (days, months, years) between two dates, useful for calculating durations, aging, or time-to-deadline.YEARYEAR extracts the four-digit year component from a date serial number, returning an integer representing the calendar year, such as 2024 or 2025.EDATEReturns a date that is a specified number of months before or after a start date, making it easy to calculate contract expirations and probation deadlines.

Frequently asked questions

Why does DATEVALUE give #VALUE! when I paste a date from an email or web page?: Emails and web pages often include extra text, time zones, or non-standard formatting around the date. Extract just the date portion (e.g., '1/15/2020') and ensure it matches your system's date format. Test with a simple, unambiguous date first.
Can DATEVALUE handle dates with times, like '1/15/2020 14:30'?: DATEVALUE extracts only the date part and discards the time. If you need the time component, use TIMEVALUE on the time portion separately, or import the full datetime as a single value using your system's datetime functions.
What's the difference between DATEVALUE and just using DATE()?: DATE(year, month, day) constructs a date from three separate numbers; DATEVALUE parses a single text string into a date. Use DATE when you have year, month, and day in separate columns; use DATEVALUE when the entire date is in one text cell.
How do I convert a whole column of text dates to real dates?: Create a helper column with =DATEVALUE(A2) and copy the formula down, then copy the results and Paste Special > Values to replace the original text with date serial numbers. In Google Sheets, BYROW(A:A, LAMBDA(x, DATEVALUE(x))) converts the entire column in one formula.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17