DATEVALUE Function – Google Sheets

The DATEVALUE function takes a date value in any valid format and returns the same date as a serial number. Spreadsheets store all dates and times as serial numbers in Google Sheets, so they will 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 needs to be either surrounded by quotes or a cell reference.

Thumbnail for a video about the DATEVALUE function
YouTube Tutorial on the DATEVALUE Function

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 date to a numeric value similar to 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 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

FormulaResult
=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

FormulaResult
=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

FormulaResult
=DATEVALUE("March 1")March 1, 2017

Just a day and a month typed in. The spreadsheet assumes the date should be the current year when we wrote this article of 2017. 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.

thumbnail for YouTube video about an employee timesheet
YouTube Video about an Employee Timesheet in Sheets

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.

3 thoughts on “DATEVALUE Function – Google Sheets”

  1. 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

    Reply
    • 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.

      Reply
      • 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.

        Reply

Leave a Comment