The DATEVALUE function accepts a date value in any valid format and returns it as a serial number. Spreadsheets store dates and times as serial numbers in Google Sheets, so they work in formulas (you can add March plus June).
You can use the DATEVALUE function to prepare dates to be sorted, filtered, or used in formulas.
The input for this function must be surrounded by quotes or be a cell reference.
Contents
Purpose
The DATEVALUE function returns a serial number from any valid date.
Syntax
=DATEVALUE(date_text)
date_text– The date, written in a text format, for the function to convert
Related functions
TIMEVALUE – Converts a time to a numeric value similar to DATEVALUE
YEAR – Returns the year value from a date
MONTH – Returns the month value from a date
DAY – Returns the day value from a date
TIMEDIF – Google Sheets Add-On
Calculate any duration in:
- Years
- Months
- Weeks
- Days
- Hours
- Minutes
- Seconds
- Milliseconds
Errors
#VALUE! – The inputs aren’t a valid date, such as “A minute ago” or “Then.”
#NUM! – An input is a number but is not a valid date.
Examples
Example 1 – Plain and Simple
| Formula | Result |
=DATEVALUE("March 1, 2017") | March 1, 2017 |
We have a date value that Sheets recognizes to return a serial number. In this case, we set the formatting to Date to show the serial number as March 1, 2017. If you set the formatting to number, the result of the function would be 42,795.00.
Example 2 – Unrecognized Text Date
| Formula | Result |
=DATEVALUE("March 1st, 2017") | #VALUE! |
Secondly, a full date typed as 1st instead of 1 returns a #VALUE! error. The #VALUE! error occurs whenever Sheets cannot figure out the text date.
Example 3 – Just a Day and Month
| Formula | Result |
=DATEVALUE("March 1") | March 1, 2022 |
Just a day and a month typed in. The spreadsheet assumes the date should be the current year when we updated this article in 2022. The year value will always be the present year when viewed in the live spreadsheet.
Example 4 – Used in an employee timesheet
Last, below is an example of this function creating an employee timesheet.
Live examples in Sheets
Go to this spreadsheet for the examples of the DATEVALUE function shown above that you can study and use anywhere you would like.
