Contact
12 programs
Explore the site

TO_DATE Function in Google Sheets: Formula, Examples & Troubleshooting

Parser Intermediate Google Sheets

Convert numeric date serials with TO_DATE in Google Sheets. Preserve fractional times, distinguish text from dates, and avoid slash-date division mistakes.

Verification status: See the evidence and test scope described in this guide. How formula examples are checked

Quick answer: TO_DATE interprets a number as a Google Sheets date serial. =TO_DATE(46023) corresponds to January 1, 2026. It is not a general-purpose parser for date-looking text.

Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Google documentation was reviewed October 17, 2026. Dates and fractional-day arithmetic were independently calculated from Google’s documented epoch; no native display was captured.

Syntax and input behavior

=TO_DATE(value)

The single required argument can be a value or a cell reference. For numeric input, Google measures days from December 30, 1899. Negative numbers refer to earlier dates; the fractional part describes a time after midnight. For nonnumeric input, TO_DATE returns the value unchanged. Google TO_DATE reference.

The function changes how a numeric serial is interpreted and displayed. It does not know which date system a file exporter intended. A number of Unix seconds, an eight-digit calendar label, and a Sheets date serial are three different encodings even when all are stored as numbers.

Example: interpret an exported serial column

Create this input table. Column B should contain the numbers shown, rather than text labels that only look numeric.

Row A: Record B: Serial
1 Record Serial
2 Opening date 46023
3 Midday event 46023.5
4 Next morning 46024.25
  1. Put Converted value in C1.
  2. Enter =TO_DATE(B2) in C2 and fill down to C4.
  3. Display C2:C4 using a date-and-time format so fractional days remain visible.
  4. Compare the calendar meanings with the expected results below; the exact punctuation and month order depend on display settings.
Cell Independently calculated calendar meaning
C2 January 1, 2026, 00:00
C3 January 1, 2026, 12:00
C4 January 2, 2026, 06:00

The 0.5 in B3 is half of a 24-hour day. The 0.25 in B4 is six hours. Formatting a result as a date without a time may hide these fractions, but a hidden fraction is not evidence that it was removed.

For a quick time-difference check, put =(B3-B2)*24 in D3. The expected result is 12 hours. This uses the underlying serials and does not depend on whether the date display shows an hour field.

Text and slash expressions are different cases

If A7 contains the text pending, =TO_DATE(A7) returns that text unchanged. A date-looking text string is likewise not promised to be parsed by TO_DATE. Use an explicit DATE construction for known year, month, and day components, or investigate DATEVALUE when the source is genuinely a date string.

Do not type a slash date as an unquoted arithmetic expression:

=TO_DATE(10/10/2000)

Google documents this as division, yielding the input 0.0005. On the documented epoch, that is December 30, 1899 plus 43.2 seconds, not October 10, 2000. If the intended date is October 10, 2000, construct it explicitly as =DATE(2000,10,10).

Quoting the slash expression does not make TO_DATE a date parser: it changes the input into text, which falls under the unchanged-value rule. Choose the conversion function from the source data’s actual representation.

Advanced use: validate the source encoding before conversion

Keep the raw export column intact and add a separate converted column. Add three known reference records to your import check:

Numeric input Expected calendar date
0 December 30, 1899
2 January 1, 1900
25569 January 1, 1970

These values are independently derived from the documented Sheets epoch. They are useful calibration points when an import is shifted by a day or produces an implausible year. If the exporter uses another epoch or time unit, determine that convention before converting the entire column. Do not fix a mysterious offset by adding an unexplained constant.

A negative test is also informative: serial -1 corresponds to December 29, 1899. Negative input is not inherently an invalid date under Google’s documented TO_DATE contract.

Troubleshooting

  • The cell still contains text: That can be the documented result for nonnumeric input. TO_DATE is not a guarantee that every result is a date value.
  • The result appears as a large number: Inspect the display format and confirm the formula is in the intended cell.
  • The day looks correct but elapsed time is wrong: Inspect fractional days. A date-only display can conceal hours.
  • The date is far in the future: Confirm the input is in days, not seconds or milliseconds, and check the exporting system’s epoch.
  • Month and day appear reversed: Separate the underlying date from its display format. Do not change numeric data merely to match a preferred visual order.

The current Google reference establishes documented support on the review date. An introduction date and retirement or last-working version were not established. No Excel date-system parity or numeric-text coercion beyond Google’s explicit rule is assumed here.

See DATE, DATEVALUE, TEXT, and N for related construction, parsing, display, and numeric-conversion tasks.

First-party references reviewed October 17, 2026: TO_DATE, DATE, and spreadsheet settings.