Google Sheets TO_DATE Function: Syntax, Examples & Tips

What Is TO_DATE?

TO_DATE takes a number and displays it as a date. Google Sheets stores dates as serial numbers — the count of days since 30 December 1899 — so the number 46023 is 1 January 2026. TO_DATE applies date formatting to that number so you see a readable date instead of the raw serial.

The underlying value doesn't change: it's still 46023, which means you can keep adding days, subtracting dates, or comparing them.

How Date Serial Numbers Work

Fractions represent time of day: 46023.5 is noon on 1 January 2026. TO_DATE shows the date portion; to display time as well, apply a date-time format to the cell.

Basic Examples

Display a serial number as a date

=TO_DATE(46023)

Shows 1/1/2026 (or your locale's equivalent).

Fix a formula that shows a number instead of a date

=TO_DATE(A2 + 30)

Adding to a date sometimes yields a plain number if the cell isn't formatted. Wrapping in TO_DATE forces date display — here, 30 days after the date in A2.

Convert dates from a numeric export

=ARRAYFORMULA(TO_DATE(B2:B500))

Some systems export dates as serial numbers. This converts the whole column for display.

Convert from MAX or MIN

=TO_DATE(MAX(A2:A500))

MAX of a date column often shows a number in a fresh cell; TO_DATE presents it as the latest date.

What TO_DATE Does Not Do

• It doesn't parse text. If a cell contains the text "2026-10-26" or "Oct 26, 2026", TO_DATE returns it unchanged. Use DATEVALUE to convert date text into a serial number, then format it.
• It doesn't convert Unix timestamps directly. Unix time counts seconds since 1 January 1970. Convert first: =TO_DATE(A2/86400 + DATE(1970,1,1)). For milliseconds, divide by 86,400,000. (Sheets also has EPOCHTODATE for this.)
• It doesn't build dates from parts. To combine a year, month and day, use DATE(year, month, day).

Common Use Cases

• Cleaning exports where dates arrive as serial numbers.
• Formatting results of date arithmetic, MAX/MIN, QUERY or array formulas.
• Converting timestamps from other systems once adjusted to the Sheets epoch.
• Debugging date logic by flipping between TO_DATE and TO_PURE_NUMBER.

Common Errors

• Result shows 12/30/1899: The input was 0 or blank. Check the reference.
• Text comes back unchanged: The source value is a string, not a number. Use DATEVALUE first: =TO_DATE(DATEVALUE(A2)).
• Dates decades off: Excel for Mac (1904 system) or Unix timestamps use different starting points. Adjust by the correct offset before converting.
• Wrong day/month order: Display order depends on the spreadsheet's locale (File → Settings).

Tips and Best Practices

• Use TO_DATE in formula output; for regular data cells, set Format → Number → Date instead.
• Pair with TO_PURE_NUMBER to switch between the two views when troubleshooting.
• Use INT to remove a time portion before comparing dates.

Related Functions

DATE, DATEVALUE, EPOCHTODATE, TODAY, TO_PURE_NUMBER, TO_TEXT, and TEXT.