How to Use TO DATE Function in Google Sheets

The TO_DATE function in Google Sheets turns a given number into a date. It converts a date in numeric value into a date.  

This helps people and small businesses who don’t have a payroll system easily add the dates by turning the numbers that represent the dates into actual dates. 

Before writing the TO_DATE function, you must know its rules:

• The function has only one argument, the value.
• If the value is a positive numeric value, the function turns the value into a date. 
• If the value is a negative numeric value, the TO_DATE interprets it as a day or days before this date.
• If the value is factorial, the TO_DATE interprets it as a time of the day after midnight. 
• If the value is alphanumeric, the function can not interpret it and will return it without any modification. 
• The TO_DATE can only interpret dates from December 30, 1899, onwards.

Let’s check the example below. 

A business doesn’t have any payroll system and uses Google Sheet to track its employees’ schedules and attendance. Unfortunately, someone messed up the record, and all dates turned into numbers and decimals. Here’s 4 out of 100 employees whose attendance was affected.

Since the team uses Google Sheets, they can quickly revert the data to dates using the TO_DATE function.

The TO_DATE function can quickly turn the data into its actual value error-free as long as the values are within the function’s range.

Though there is another way to convert the dates in Google Sheets, only the TO_DATE function can convert and generate fractional numbers to time and negative numbers to date. 

Before we do anything, let us have a quick look at the function’s fundamentals.

The Anatomy of the TO_DATE Function in Google Sheets

The syntax of the formula is shown below:

Next, we will learn the parts of the function.

• = the equal sign starts the TO_DATE function in Google Sheets.
• TO_DATE() is our TO_DATE function. 
• Value refers to the given number or reference cell that we must convert into a date.

A Real Example of Using TO_DATE Function in Google Sheets

Since we already know the function’s rules, syntax, and parts, we will now solve the above sample problem. 

Here are the data that we need to convert.

The next step that we will do is to convert the numbers into a date by using the formula below.

After using the formula, it will now show the results like this:

If you wonder how the function converted those numbers, I will teach you how. You may click on the link below to use the same spreadsheet that I am using.  

Let us now start writing the function by following the guide below.

How to Use TO_DATE Function in Google Sheets

1. The first step that we should do is to ensure that the given data that needs to be converted are all in the specified cells.
2. The second step is to notify Google Sheet that what we are doing is we are trying to create a function. Following the anatomy of the function, we will start the function with an equal ‘=‘ sign.

If a pop-up appears suggesting a function, cancel it by pressing the ‘BACKSPACE’ on your keyboard.

3. The cell result must only have an equal ‘=‘ sign before adding the TO_DATE function.

Note: Though our TO_DATE function is TO_DATE(), we will only write the function name and the open ‘(‘parenthesis. Then, it will be followed by the given value or reference cell. 

4. After adding the first part of the function, we will move to the middle part, which is the value. We will substitute the value with the numbers under the date column.

You can manually add the numbers or by selecting the reference cell containing the value. Either way, once you have added all the values, you must add the closing’)‘ parenthesis to complete the function.  

5. Lastly, press the ‘Enter’ key on our keyboard to get the returning value. After using the TO_DATE function, the result shows the date from converted numbers.

Frequently Asked Questions

What is the difference between Google Sheets’ TO_DATE function and DATE function?

The TO_DATE function converts a number into a date, while the DATE function’s resulting value comes from converting a year, a month, and a day into a date.

How to write the TO_DATE function?

The syntax of the function contains only 1 argument, the value. We can substitute the value by using the actual number or the reference cell that contains the value.

Formula:

Example:

Can the TO_DATE function convert -number and factorial numbers into a date?

Yes, if the value is negative, it can be interpreted as days before a specific date. For example, when you convert the number 1 into a date, the returning value will be 12/31/1899. But if you convert the -1 into a date, the returning value will show as 12/29/1899.

Summary

Using the TO_DATE function might be a little bit mind-boggling as it is not how we usually turn the numbers into dates in Google Sheets. However, the TO_DATE function is helpful if you mess up the dates and turn them into numbers alien to you. 

To easily remember how the function works, you must remember these things by heart.

1. The function has only 1 argument, which is the value. The value is the number that we need to convert into a date.
2. If the value is positive, it will be converted into a specific date which can be ‘today’ or a future date.
3. If the value is negative, the TO_DATE function converts it into a date representing ‘yesterday’ or days before ‘this’ date.
4. Do not worry if the numbers look like this 43463 than the regular numbers representing the dates like 10142010 (October 14, 2010) or 05092010(May 09. 2010). The function can convert the 43463, which is December 29, 2018.

Just remember the fundamentals of the TO_DATE function, and you can easily convert the numbers back into dates! If you enjoyed this one, you can check our site for other Google Sheets functions and subscribe to our newsletter to get new updates.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe