# Google Sheets TO_DATE Function: Syntax, Examples & Tips - URL: https://betteranalyst.com/google-sheets/todate-function - Author: Better Analyst Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation. [Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst) Converts a provided number to a date. ``` =TO_DATE(value) ``` Generate FormulaExplain Formula One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward. Describe the TO\_DATE you need in plain EnglishGenerate Formula ## Parameters | Parameter | Required | Description | | - | - | - | | **value** | Yes | The number, or reference to a cell containing a number, to display as a date. Whole numbers are days since 30 December 1899. Non-numeric values are returned unchanged. | ## 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 | Serial number | Displayed date | | - | - | | 1 | 31 Dec 1899 | | 2 | 1 Jan 1900 | | 45658 | 1 Jan 2025 | | 46023 | 1 Jan 2026 | 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](https://betteranalyst.com/google-sheets/topurenumber-function) 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](https://betteranalyst.com/google-sheets/topurenumber-function), [TO\_TEXT](https://betteranalyst.com/google-sheets/totext-function), and TEXT. ## Example use case An analyst pulls a CRM export where the close date column contains numbers like 46012. Adding a helper column with =ARRAYFORMULA(TO\_DATE(D2:D)) turns them into readable dates that can be grouped by month in a pivot table. ## Need more help with Google Sheets formulas? Sign up for Better Analyst and get unlimited AI-powered formula generation, explanations, and advanced data analysis. [Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai) ## Google Sheets how-to guides - [How To Highlight Duplicates In Google Sheets](https://betteranalyst.com/google-sheets/how-to-highlight-duplicates-in-google-sheets) - [How To Insert Checkbox In Google Sheets](https://betteranalyst.com/google-sheets/how-to-insert-checkbox-in-google-sheets) - [How To Insert Multiple Rows In Google Sheets](https://betteranalyst.com/google-sheets/how-to-insert-multiple-rows-in-google-sheets) - [How To Lock A Row In Google Sheets](https://betteranalyst.com/google-sheets/how-to-lock-a-row-in-google-sheets) - [How To Lock Cells In Google Sheets](https://betteranalyst.com/google-sheets/how-to-lock-cells-in-google-sheets) - [How To Make A Copy Of An Google Sheets Sheet](https://betteranalyst.com/google-sheets/how-to-make-a-copy-of-an-google-sheets-sheet) - [How To Make A Graph In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-graph-in-google-sheets) - [How To Make A Histogram In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-histogram-in-google-sheets) ## Explore all Google Sheets formulas Browse our complete library of Google Sheets formula guides Compare your options ## Decide with evidence, not a sales pitch. Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose. 1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.