The To_date function in Google Sheets is categorized under the Parser functions. Learn how to use this very useful Spreadsheet function with my formula examples.

I have used or already mentioned about the To_date function so many times in my tutorials on this site. But I missed writing a dedicated tutorial on the use of this function.

Why so?

Actually, I have written a lengthy tutorial that covers the use of all Google Sheets Date related functions. I thought it contains the above-said function. But recently realize that it was not there.

I missed to include To_Date function in that tutorial because it’s categorized under Parser not Date in Google Sheets.

So here in this tutorial, I am going to detail you how to use the To_date function in Google Sheets.

Understand the To_date Function in Google Sheets

Syntax:

TO_DATE(value)

Purpose:

The To_date function in Google Docs Sheets converts a provided number to a date interpreting the value as the number of days since 30-Dec-1899.

That means =to_date(0) returns the above date and =to_date(1) returns 31-Dec-1899. What about negative numbers then?

Negative values like =to_date(-1) interpreted as days before this date.

Can I use fractional values in To_Date function in Google Sheets?

Yes! Fractional values in To_date function indicate the time of day past midnight. For example, see this formula.

=to_date(1.5)

It returns 31/12/1899 12:00:00pm, if properly format the output column from the format menu in Sheets.

Why should one convert a number to date in Google Sheets?

The real purpose of To_date function is to convert a date value back to date. Not any number to date. I’ll try to explain this in the below section.

How to Use the To_Date Function with Other Functions in Google Sheets

I am going to give you some examples of the practical use of Google Sheets To_date function.

The Use of To_Date with Int Function in Google Sheets

First one question to you. How to remove time from date time aka timestamp in Google Sheets?

Maybe you know the answer. It’s by using the INT function. Want to see an example? Here you go!

Date Time in cell A2: 27/02/2019 10:50:44

The below formula would return the output as a number correspond to the date value in cell A2 and that is 43523.

=int(A2)

Here is a more detailed tutorial on this – How to Extract Date From Time Stamp in Google Sheets.

To convert this date value back to date you can use the To_date Function in Google Sheets as below.

=to_date(int(A2))

Converting Date in Text Format to Number in Google Sheets

Using the Isdate function you can check whether a date in a cell is a proper date or not. It would return TRUE or FALSE based on the value.

Even if the date is formatted as a plain text, the Isdate function will consider that as a date and return TRUE. You decide whether it’s good or bad. I am not going into those details now.

See the example below to understand dates formatted as text in Google Sheets.

Example to Date Entered as Text:

In the above example, the first date in cell A1 is entered as a text. See the leading apostrophe, so that’s text. In cell A2 the date is formatted as pure text from Format > Number > Plain text.

That means cell A1:A2 contain date in text format, right? Why should one convert such dates, i.e. date in text format, back to date?

To understand the issue, just count these dates (I call it dates because the Isdate function identifies it as dates) using the COUNT function.

=count(A1:A2)

It would return zero!

Here you can use the below formula to convert the date in text format to proper date.

=to_date(datevalue(A1))

In an array, you can use it as below.

=ArrayFormula(to_date(datevalue(A1:A2)))

Now use the count function. It would count the dates properly.

=ArrayFormula(count(to_date(datevalue(A1:A2))))

Now here is another example to the use of To_date function in Google Sheets.

ArrayFormula with Google Sheets To_Date Function

In the above example, I have used the To_date function in the array using the function ArrayFormula. Here is another example.

I have a mixed data type column. In that some cells contain dates and some other cells contain strings.

Must Read: How to Solve the Mixed Data Type Issue in Query in Google Sheets.

See how to extract the dates using an ArrayFormula that involves the To_Date function.

Here is that ArrayFormula with To_Date.

=ArrayFormula(to_date(iferror(datevalue(A1:A))))

In this;

1. The Datevalue function returns the dates in column A as date values.
2. The Iferror removes the error that appears instead of text strings.
3. Finally, the To_Date function converts that date values to proper dates.

But the better formula to extract dates from a mixed column is not the above one. Use the below Filter formula for that.

=filter(A1:A7,iferror(datevalue(A1:A7))<>"")

Follow the above examples to understand how to use the To_date function in Google Sheets. Enjoy!

Additional Resources:

1. How to Compare Time Stamp with Normal Date in Google Sheets.
2. Convert Date to String Using the Long-winded Approach in Google Sheets.
3. Combine Text and Date in Google Doc Spreadsheet Using Formula.
4. Find the Last Entry of Each Item from Date in Google Sheets.
5. Find the Past or Future Closest Date to Today in Google Sheets.
6. Elapsed Days and Time Between Two Dates in Google Sheets.