To_date Function in Google Sheets

The TO_DATE function in Google Sheets is one of those handy parser functions that converts a number into a valid date format. Let’s explore how to use this function, starting with its syntax followed by some real-life use cases.

Syntax of the TO_DATE Function

TO_DATE(value)

value – The numeric value to convert into a date.

The TO_DATE function in Google Sheets interprets the number as the number of days since December 30, 1899.

That means:

=TO_DATE(0) returns 30-Dec-1899
=TO_DATE(1) returns 31-Dec-1899

What about negative numbers?

Try =TO_DATE(-1) — it returns the day before 30-Dec-1899. Yes, it supports negative values too.

Can I Use Fractional Values in the TO_DATE Function in Google Sheets?

Absolutely! Fractional values represent the time of day past midnight.

For example:

=TO_DATE(1.5)

If your column is formatted properly (Format > Number > Date time), this would return:

31/12/1899 12:00:00 PM

What If the Value Is a Text?

If the input is a text string, the TO_DATE function in Google Sheets simply returns the value as-is without any conversion.

Examples of Using the TO_DATE Function in Google Sheets

Let’s dive into a few practical examples.

1. Using INT

Suppose cell A2 contains a date-time value like 27/02/2019 10:50:44.

This formula:

=INT(A2)

Returns 43523, the serial number representing the date. To convert it back to a date:

=TO_DATE(INT(A2))

Voilà! The TO_DATE function makes it human-readable again.

2. With DATEVALUE

If you have a text-formatted date in cell A1, use this combo:

=TO_DATE(DATEVALUE(A1))

When you’re working with a column that has a mix of numbers, dates, and text, you can extract only the valid dates like this:

=ArrayFormula(TO_DATE(IFERROR(DATEVALUE(A2:A))))

This returns only date values, while others show as blank. To get a clean list of dates, wrap it in TOCOL:

=ArrayFormula(TOCOL(TO_DATE(IFERROR(DATEVALUE(A2:A))), 3))

3. With SEQUENCE

You can even create a column of sequential dates using SEQUENCE. Here’s how:

=SEQUENCE(10, 1, DATE(2025, 1, 1), 1)

This returns 10 serial numbers starting from 45658, which represents 01/01/2025.

To convert those serial numbers into proper dates:

=ArrayFormula(TO_DATE(SEQUENCE(10, 1, DATE(2025, 1, 1), 1)))

This will return a date list from 01/01/2025 to 10/01/2025.

Conclusion

The TO_DATE function in Google Sheets is a simple but powerful tool that lets you convert numeric or parsed date values back into a readable date format. It works great with INT, DATEVALUE, SEQUENCE, and even when cleaning up mixed-type data columns. Once you understand it, turning numbers into dates becomes seamless.

How to Use the TO_DATE Function in Google Sheets