This guide covers everything you need to know about the Google Sheets TO_DATE function, including its definition, syntax, use cases, and how to use it.
What is the TO_DATE Function? How Does It Work?
The TO_DATE function in Google Sheets converts a provided numeric value into a date format. For example, if you have a cell with the number 44198 (representing the date in Google Sheets’ serial number format), using the TO_DATE function will convert this number into a date, like “2021-01-01”. The formula to achieve this would look something like =TO_DATE(44198).
This function is especially useful when dealing with data imported from different systems that might represent dates as serial numbers. The TO_DATE function translates these numbers into a more human-readable date format.
In essence, TO_DATE offers a straightforward method to interpret numeric data as dates, simplifying the process of working with diverse datasets in Google Sheets.
TO_DATE Syntax
The syntax and arguments for the function are as follows:
TO_DATE(value)
Here, the ‘value’ argument represents the argument or reference to a cell to be converted to a date.
Important points to note about the syntax and arguments of this function include:
- TO_DATE does not autoconvert number formats in the same way as direct entry into cells. This means that if you input TO_DATE(10/10/2000), it will be interpreted as TO_DATE(0.0005), the quotient of 10 divided by 10 divided by 2000. This is a crucial point to remember to avoid errors in your date conversion.
- The TO_DATE function is not used as commonly as the DATE function, which takes a year, month, and day in numeric format as inputs. This is because the DATE function allows for more specific date inputs, while TO_DATE converts a number into a date.
- TO_DATE is the inverse of the N function when applied to a date. If you use the N function to convert a date to a number, you can use TO_DATE to convert that number back into a date
Examples of How to Use the TO_DATE Function
Here are some practical examples of how to use the TO_DATE function in Google Sheets.
Example #1: Converting a Text String to a Date
Let’s say you have a text string “s” in cell A1 and want to convert it to a date. Use the following formula:
=TO_DATE(A1)
This formula will convert the text string “2023-12-25” into a date, and the cell will display “12/25/2023”.
Example #2: Converting a Number to a Date
Imagine you have a number 44204 in cell B1. This number represents the number of days since December 30, 1899, a standard base date for date calculations. To convert this number to a date, use the formula:
=TO_DATE(B1)
This formula will convert the number 44204 into a date, and the cell will display “1/1/2021”.
Example #3: Converting a Text String with a Custom Format to a Date
Suppose you have a text string “25-12-2023” in cell C1 and want to convert it to a date. However, this text string is in a different format (day-month-year). You can still convert it to a date using the TO_DATE function in combination with the DATEVALUE function. Use the following formula:
=TO_DATE(DATEVALUE(REPLACE(C1, 3, 0, “/”)&REPLACE(C1, 1, 2, “/”)&LEFT(C1, 2)))
This formula will convert the text string “25-12-2023” into a date, and the cell will display “12/25/2023”.
Example #4: Using TO_DATE in Combination with Other Functions
The TO_DATE function can be used in combination with other functions. For example, you can use it with the TODAY function to calculate the number of days between today’s date and a given date. Let’s say you have a date “2023-12-25” in cell D1. Use the following formula:
=TO_DATE(D1)-TODAY()
This formula will calculate the number of days between “2023-12-25” and today’s date. If today’s date is “12/1/2023”, the cell will display “24”.
Why Is TO_DATE Not Working? Troubleshooting Common Errors
You might encounter a few common errors if you’re using the TO_DATE function in Google Sheets and it’s not working as expected. Here, we’ll discuss these errors, their causes, and how to fix them.
#VALUE! Error
Cause: This error often occurs when the TO_DATE function is unable to understand the data format provided. It can’t convert the given value to a date.
Solution: Make sure the data format you’re using is understandable to the TO_DATE function. Google Sheets recognizes dates in the format of MM/DD/YYYY. Consider changing it to this format if you’re using a different format.
#N/A Error
Cause: If you’re seeing the #N/A error, it’s likely because TO_DATE function can’t find the value you’re referencing. This could be due to a misspelled range or a non-existent range.
Solution: Double-check the range you’re referencing. Make sure it’s spelled correctly and exists in your sheet. If referencing another sheet, ensure you’ve provided the correct name.
#REF! Error
Cause: The #REF! error usually appears when your formula references a cell that does not exist. This might be because you’ve deleted a row or column referenced in the formula.
Solution: You can solve this by updating your formula to reference a cell that exists. Make sure you’re not referencing any cells that have been deleted.
#NUM! Error
Cause: This error occurs when you’re trying to convert a value that’s not a number or a valid date string to a date.
Solution: Check the values you try to convert with the TO_DATE function. If you’re trying to convert a number, ensure it’s valid. If you’re trying to convert a date string, ensure it’s formatted correctly.
#DIV/0! Error
Cause: This error is a result of a division by zero within your formula.
Solution: Review your formula to make sure there aren’t any divisions by zero. If there are, you’ll need to adjust your formula so that zero isn’t the denominator in any division operation.
#ERROR! Error
Cause: This general error can occur when your TO_DATE function is not formatted correctly. This could be due to incorrect syntax or missing or incorrect arguments.
Solution: Review the syntax of your TO_DATE function. Ensure that all required arguments are present and that you’re using the correct arguments. If you’re unsure, refer back to the syntax and examples provided in this guide.
For more details on the TO_DATE function, check out the official documentation at the Google Docs Editors Help Center.
