The DATEVALUE function takes a date value in any valid format and returns the same date as a serial number. All dates and times are stored as serial in Google Sheets so they will work in formulas (you can add March plus June).
The DATEVALUE function can be used to prepare dates to be sorted, filtered, or used in formulas.
The input for this function needs to be either surrounded by quotes or a reference to another cell with a date in it.
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, that is to be converted
Related functions
TIMEVALUE – Converts a date to a numeric value in a similar way as DATEVALUE.
HOUR – Returns the hour value from a time
MINUTE – Returns the minute value from a time
SECOND – Returns the second value from a time
Errors
#VALUE! – The inputs are something that isn’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 |
This example has a date-value that Sheets recognizes so it is able to return a serial number. In this case, the formatting is set to date, so the serial number is shown as March 1, 2017. If the formatting were set to number, the result of the function would be 42,795.00.
Example 2 – Unrecognized text date
| Formula | Result |
| =DATEVALUE(“March 1st, 2017”) | #VALUE! |
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, 2017 |
Just a day and a month typed in. The spreadsheet assumes the date should be the current year which was 2017 when this was written. The year value will always be current when viewed in the live spreadsheet.
Example 4 – Used in an employee timesheet
Below is an example of this function being used to create 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.
Tom Wright says:
Do you happen to do any consulting work? I’ve learned a ton from your videos but cant quite get a date formula I need figured out. I
Tom Wright says:
Basically I’m trying to insert a due date associated with as task with a check mark column next to it, if the check mark column doesn’t get clicked by the due date I trying to get the cell to turn red. Thanks in advance, really love this website.
Super User says:
As far as doing it with a pre-made formula – sort of, but not really. You could use a combination of an IF formula, and if the checkmark’s value is TRUE, you could then use the TODAY function. However, the TODAY function updates to the current date every time you open the Sheet.
You want more of a timestamp. I think that would be a custom script. Consulting, sure. Let me know what you have in mind if you’re still looking.