The purpose of the DATEVALUE function in Google Sheets is to convert a date string into a date value. However, it can also be used for two other important purposes:
- To replace the ISDATE drag-down formula or the alternative MAP + ISDATE combo.
- To simplify the use of date conditions in QUERY.
The date value is a number that represents the date in terms of days since December 31, 1899. So the DATEVALUE will return the serial number 1 for the date string “1899-12-31”, 2 for “1900-01-01”, and so on.
Syntax of the DATEVALUE Function in Google Sheets
The syntax of the DATEVALUE function in Google Sheets is:
DATEVALUE(date_string)Where date_string is a text string that represents a date. It must be enclosed in double quotes if you hardcode it.
Example:
=DATEVALUE("05/09/23")The date string can be in any format that Google Sheets can recognize. Some recognizable date string formats include:
- 05/09/23
- Tue, 05 09 23
- Tue, 5 Sep 2023
- 2023-09-05
- 5 Sep 2023
- 5-Sep-2023
- 05/09
- Tuesday, 5 September 2023
- Tuesday, 5 September 2023, 10:10:30
- 13:25
When calculating the date value, the month and day will be determined based on your Sheets’ Locale setting, which is either MM/DM/YYY or DD/MM/YYY. You can find the date and time formatting corresponding to your locale by applying the TODAY() function in cell A1 or any blank cell in a new sheet.
For example, the date string “05/09/2023” could be interpreted as either May 9, 2023, or September 5, 2023, depending on the locale of the Google Sheets spreadsheet. The date values will be varied accordingly.
This has an exception. If the date string contains month text, such as “5 Sep 2023”, you will not encounter any issues with your Sheets’ date formatting.
Examples
In the following example, I have used the DATEVALUE function in Google Sheets to convert date strings in cell range A3:A11 to date values.
I have used the following non-array formula in cell B3 to convert the date string in cell A3 to a date value.
=DATEVALUE(A3)The formula is copied and pasted down to apply it to the rows below.
However, when you want to convert a bunch of date strings in one go, you can use an array formula in the topmost row of the range. In the above example, we can use the following DATEVALUE + ARRAYFORMULA combo in cell B3 provided B3:B11 is blank.
=ARRAYFORMULA(DATEVALUE(A3:A11))How to Convert DATEVALUE to Date in Google Sheets
There are two ways to convert a date serial number back to a date in Google Sheets.
The first method is to use the TO_DATE function with DATEVALUE.
Syntax of the TO_DATE Google Sheets Function:
TO_DATE(value)For example, the following formula would return the serial number 45174:
=DATEVALUE("Tue, 5 Sep 2023")To convert this back to a date, use the TO_DATE with DATEVALUE as follows:
=TO_DATE(DATEVALUE("Tue, 5 Sep 2023"))The second option is to use Format > Number > Date formatting the cell containing the date serial number.
When you use the TO_DATE function with a cell range, you should wrap it in the ARRAYFORMULA function as follows:
=ARRAYFORMULA(TO_DATE(DATEVALUE(A3:A11)))Other Uses of the DATEVALUE Function in Google Sheets
I mentioned the two other important uses of the DATEVALUE function earlier in this tutorial. This is exclusively for Google Sheets users. It might not work in Excel.
1. Replace ISDATE and MAP with DATEVALUE in Google Sheets
The ISDATE function returns TRUE or FALSE evaluating a value in a cell. If the value is a date, time, or timestamp, the result will be TRUE. Otherwise, the result will be FALSE.
Syntax of the ISDATE Function in Google Sheets:
ISDATE(value)
In the above example, you can see multiple ISDATE formulas in column D. You cannot use ISDATE with ARRAYFORMULA.
This is because ISDATE is an array function itself. It will take a range and return a single result. I mean, if you apply this function in a range, it will return TRUE if all the values in the range are dates, else FALSE.
=ISDATE(A3:A9)So to expand an ISDATE formula, we can use the MAP function as follows:
=MAP(A3:A9,LAMBDA(r, ISDATE(r)))
There is an alternative formula using the DATEVALUE function in Google Sheets that can even distinguish between dates and times.
We can replace the above ISDATE + MAP combo with the following DATEVALUE formula in Google Sheets:
=ARRAYFORMULA(IFERROR(DATEVALUE(A3:A9),-1)>=0)If you want the formula to return FALSE against a cell containing a time component alone (not datetime), then use the formula below.
=ARRAYFORMULA(IFERROR(DATEVALUE(A3:A9),-1)>0)The last formula will return TRUE in C3 and C9 only. All other values will be FALSE.
Important:
There is one key difference between the ISDATE and DATEVALUE functions that you should be aware of.
The ISDATE function will return TRUE if the cell value is a number or text, but the underlying value is a date (the value you see in the formula bar). So if a date is formatted as ‘Sunday’, ISDATE will evaluate to TRUE, while DATEVALUE will return an error.
2. Simplifying Date Condition in QUERY with DATEVALUE
If you are familiar with the QUERY function, you may know how difficult it can be to remember the different literals that can be used for comparisons or assignments, especially date literals.
Assume you have an expense sheet with dates in A2:A, expense types in B2:B, and beneficiary names in C2:C.
You May Like: Array Formula to Split Group Expenses in Google Sheets (Template).
To filter the table for the start and end dates specified in cells E2 and E3, respectively, you can use the following QUERY formula:
=QUERY({A2:C},"Select * where Col1 > date '"&TEXT(E2,"yyyy-mm-dd")&"' and Col1 < date '"&TEXT(E3,"yyyy-mm-dd")&"'")If you are someone who finds it difficult to remember the date literal in QUERY, you can use the following formula:
=ARRAYFORMULA(QUERY(HSTACK(DATEVALUE(A2:A),B2:C),"Select * where Col1>"&E2&" and Col1<"&E3&""))In this formula, I have used the DATEVALUE function to convert the dates in A2:A to date values. This means that even if the criteria in cells E2 and E3 are dates, we can use them as number literals in QUERY.
It will be even simpler if you select A2:A and apply Format > Number > Date formatting. Then the formula will be:
=QUERY(A2:C,"Select * where Col1>"&E2&" and Col1<"&E3&"")Related: How to Use Date Values (Date Serial Numbers) in Google Sheets Query.
Conclusion
We have seen the use of the DATEVALUE function in Google Sheets. Along with this, I assume, I have shared some helpful tips.
Before concluding, I would like to add one more thing.
Some Google Sheets formulas will automatically convert dates to date values. For example, the SEQUENCE function.
The following SEQUENCE formula will return 31 date values starting from the date value that corresponds to “2023-1-1”:
=SEQUENCE(31,1,"2023-1-1")You can use TO_DATE with it to get proper dates.
=ARRAYFORMULA(TO_DATE(SEQUENCE(31,1,"2023-1-1")))In short, DATEVALUE is not the only function that returns date values in Google Sheets. However, it is the only function that converts a date string to a date value.
