What happens if my dates are already date values, not text?

If the value is already a date, TO_DATE returns it unchanged. TO_DATE is smart enough to recognize dates and pass them through without conversion, so it's safe to apply to mixed data.

Can TO_DATE handle dates in different formats simultaneously?

TO_DATE recognizes many common formats (MM/DD/YYYY, YYYY-MM-DD, spelled-out like "January 15, 2023") and will convert any recognized pattern. However, unusual or locale-specific formats may fail; consistency in source data reduces errors.

How do I display a TO_DATE result as text?

Use TO_TEXT(TO_DATE(value)), or apply formatting within your spreadsheet. The second approach gives more control over how the date appears to users reading the result.

Is there a performance difference between TO_DATE and other date functions?

TO_DATE is optimized for conversion and is no slower than alternatives. In large datasets, consider converting entire columns once rather than calling TO_DATE repeatedly in subsequent formulas.

TO_DATE function

TO_DATE converts text or serial numbers into a date value, enabling date calculations and consistent formatting across your spreadsheet.

=TO_DATE(value)

Generate a TO_DATE formula

Describe what you need. The generator will reach for TO_DATE where TO_DATE is the right tool, and tell you when it is not.

Arguments

How TO_DATE reads its arguments

Argument	Required	Description
value	Required	A text string, number, or cell reference representing a date in any common format; unrecognizable values return #VALUE!.

Returns

A date value that can be formatted as text, used in date arithmetic, or displayed in any date format.

Availability

Excel: Not available · Google Sheets: Supported

Worked examples

1. Convert hire date text to a date value

Employee ID	Name	Hire Date (Text)	Hire Date (Date Value)
2	Bob Smith	3/22/2019	=TO_DATE("3/22/2019")

=TO_DATE("3/22/2019")

Result: 3/22/2019 (recognized as a date value, not text)

TO_DATE interprets the text string "3/22/2019" and converts it to a genuine date value. Once converted, this date can participate in calculations like adding days, finding differences, or being formatted. Without TO_DATE, the text would remain text and many date operations would fail.

2. Convert cell text references to dates

Employee ID	Name	Hire Date (Text)	Hire Date (Converted)
5	Elena Martinez	2/10/2022	=TO_DATE(C2)

=TO_DATE(C2)

Result: 2/10/2022 (as a date value)

When data is imported or pasted, hire dates often arrive as text strings in cells. By wrapping the cell reference C2 in TO_DATE, you convert that text to a proper date value. This is essential when working with imported datasets where dates must function in formulas and comparisons.

3. Calculate years of tenure from hire dates

Employee ID	Name	Hire Date Text	Tenure (Years)
1	Alice Johnson	1/15/2020	=IFERROR(INT((TODAY()-TO_DATE(C2))/365.25),"Invalid date")

=IFERROR(INT((TODAY()-TO_DATE(C2))/365.25),"Invalid date")

Result: 4 (approximate years of tenure as of 2026)

This formula converts the hire date text in C2 to a date value, subtracts it from TODAY() to get days employed, then divides by 365.25 to calculate tenure in years. The IFERROR wrapper handles any dates that TO_DATE cannot parse, returning a readable error message instead of #VALUE!.

Common errors

Which TO_DATE error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The input text does not match any recognized date format, such as "not-a-date", "15/2023" (missing month), or using unusual separators like "15@2023@2020".	Reformat the input using a standard pattern (MM/DD/YYYY, YYYY-MM-DD, or spelled-out like "January 15, 2023"), or check the original data for typos and leading/trailing spaces.
#N/A	The cell reference passed to TO_DATE contains a blank cell, an error like #REF!, or truly empty content that cannot be interpreted as any date.	Verify the cell has actual date content; wrap TO_DATE in IFERROR to provide a fallback value, or use IF(C2="","",TO_DATE(C2)) to skip empty rows.
#NUM!	A numeric value outside the valid date serial range is passed (such as -5 or 999999999), exceeding the limits of the spreadsheet's date system.	Check that numeric inputs are within the valid range (typically 0–99999, representing dates from late 1899 onward); discard or cleanse outliers.

Tips and when to use something else

Use TO_DATE immediately after importing data from CSV or databases where dates arrive as text; it ensures all downstream formulas treat them as dates, not strings.
TO_DATE is Google Sheets–specific; Excel users should use comparable alternatives. Always verify your formula works in your target platform before building on it.
Combine TO_DATE with SPLIT when dates are embedded in longer text, like extracting "2/10/2022" from "Hired: 2/10/2022" before converting to a date.
When dealing with uncertain or messy data, pair TO_DATE with IFERROR to catch parsing failures and provide user-friendly error messages rather than formula errors.

Related functions

TO_TEXTConverts a value to text format, returning its content as plain text regardless of the original data type or formatting.SPLITSPLIT breaks a text string into an array of substrings by dividing on a specified delimiter or by individual characters.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.

Frequently asked questions

What happens if my dates are already date values, not text?: If the value is already a date, TO_DATE returns it unchanged. TO_DATE is smart enough to recognize dates and pass them through without conversion, so it's safe to apply to mixed data.
Can TO_DATE handle dates in different formats simultaneously?: TO_DATE recognizes many common formats (MM/DD/YYYY, YYYY-MM-DD, spelled-out like "January 15, 2023") and will convert any recognized pattern. However, unusual or locale-specific formats may fail; consistency in source data reduces errors.
How do I display a TO_DATE result as text?: Use TO_TEXT(TO_DATE(value)), or apply formatting within your spreadsheet. The second approach gives more control over how the date appears to users reading the result.
Is there a performance difference between TO_DATE and other date functions?: TO_DATE is optimized for conversion and is no slower than alternatives. In large datasets, consider converting entire columns once rather than calling TO_DATE repeatedly in subsequent formulas.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17