How do I check if a cell contains a date using TYPE?

Dates are stored as numbers internally, so TYPE(date_cell) returns 1, the same as any number. You cannot distinguish dates from numbers with TYPE alone. If you need to verify a column contains dates, check the cell formatting instead or use ISDATE() if available in your spreadsheet application.

Why does TYPE return 1 for both numbers and dates?

Spreadsheets store dates as serial numbers (the count of days since January 1, 1900, for example). TYPE detects the underlying numeric storage, not the display format. The date '2026-08-20' is stored as a number, so TYPE correctly returns 1. The calendar display is just formatting applied on top.

Can I use TYPE to detect empty cells?

No. TYPE returns 1 for an empty cell (treating it as zero), so you cannot distinguish blank from filled cells using TYPE. Use ISBLANK(cell) to check if a cell is empty; use TYPE for checking whether non-empty cells contain numbers, text, or errors.

What's the difference between TYPE(value)=16 and ISERROR(value)?

Both detect errors, but ISERROR returns TRUE/FALSE (simpler logic), while TYPE returns 16 as a numeric code. For most error handling, use ISERROR or IFERROR. Use TYPE only if you need the type code for complex branching that distinguishes between multiple type categories.

TYPE function

Returns a number indicating the data type of a value: 1 for numbers, 2 for text, 4 for logical values, 16 for errors, and 64 for arrays.

=TYPE(value)

Generate a TYPE formula

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

Arguments

How TYPE reads its arguments

Argument	Required	Description
value	Required	Any cell reference, formula, or literal value. TYPE accepts any value without error, even if the value itself is an error code like #DIV/0!, and returns the appropriate type number.

Returns

A number representing the data type: 1 (number), 2 (text), 4 (logical), 16 (error), or 64 (array).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Validate that revenue data is numeric

Customer	Plan	MRR	Signup Date	Churn Date
Acme Corp	Pro	500	2024-01-15
Tech Inc	Enterprise	2000	2023-06-01	2026-08-20

=TYPE(C2)

Result: 1

C2 contains 500, a number, so TYPE returns 1 (the type code for numbers). This confirms the MRR column stores revenue correctly as numeric values. If a cell accidentally contained text like '500', TYPE would return 2, alerting you to a data quality issue.

2. Verify customer names are text, not numbers

Customer	Plan	MRR	Signup Date	Churn Date
Acme Corp	Pro	500	2024-01-15
Tech Inc	Enterprise	2000	2023-06-01	2026-08-20

=TYPE(A2)

Result: 2

A2 is 'Acme Corp', a text string, so TYPE returns 2 (the type code for text). This helps audit your customer data to ensure names are stored as text. If a cell had been accidentally formatted as a number, TYPE would return 1, warning you to fix the data type.

3. Distinguish active customers from churned accounts

Customer	Plan	MRR	Signup Date	Churn Date
Acme Corp	Pro	500	2024-01-15
Tech Inc	Enterprise	2000	2023-06-01	2026-08-20

=IF(ISBLANK(E2),"Active",IF(TYPE(E2)=1,"Churned","Format error"))

Result: Active

E2 is empty for Acme Corp (no churn date), so ISBLANK returns TRUE and the result is 'Active'. For Tech Inc in row 3, E3 contains '2026-08-20' (a date stored as a number), so TYPE(E3)=1 and returns 'Churned'. This pattern categorizes customers by status while validating that dates are properly formatted.

Common errors

Which TYPE error are you seeing?

Error	Why it happens	How to fix it
#NAME?	Misspelling the function as TYPES, TYP, TYPE$, TYPEOF, or any other variant instead of the correct name TYPE.	Use the exact function name TYPE with no extra characters, prefixes, or suffixes. Check the formula bar for typos and correct to =TYPE(value).
#VALUE!	Providing incorrect arguments: either omitting the argument entirely TYPE(), or passing multiple arguments TYPE(A1,B1). TYPE requires exactly one value.	Provide exactly one argument: =TYPE(C2) to check a single cell. Do not use ranges like =TYPE(C:C) or multiple cells; use TYPE once per cell.
#REF!	A referenced cell becomes invalid when the row or column it points to is deleted from the worksheet. The formula =TYPE(D5) breaks if row 5 is deleted.	Update the formula to reference a valid cell, or use robust references like named ranges or table column headers that survive row/column deletions.

Tips and when to use something else

Dates are returned as type 1 (number) because spreadsheets store dates as numeric values internally. Use ISDATE() or check cell formatting instead if you need to specifically verify date columns.
In most cases, prefer ISNUMBER, ISTEXT, ISBLANK, and ISERROR over TYPE for simpler, more readable formulas. Reserve TYPE for advanced scenarios where you need the actual type code.
TYPE never errors, even when evaluating error values like #DIV/0! or #REF!—it returns 16 for any error. This makes it safe for nested formulas, though ISERROR is often clearer for error handling.
Type 64 (array) is rare in typical spreadsheets and mostly applies to modern Excel array formulas. Most daily work involves types 1, 2, and 4; ignore type 64 unless you're using advanced array functions.

Related functions

ISNUMBERChecks whether a value is a number, returning TRUE for numeric values and FALSE for text, dates, blanks, and errors.ISTEXTReturns TRUE if a value is text, FALSE otherwise. Use ISTEXT to validate data types or filter columns before performing calculations.ISBLANKISBLANK returns TRUE if a cell is completely empty, FALSE if it contains any value or formula, and propagates errors.ISERRORISERROR returns TRUE if a value is an error like #N/A or #REF!, FALSE otherwise, enabling error detection and handling.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

How do I check if a cell contains a date using TYPE?: Dates are stored as numbers internally, so TYPE(date_cell) returns 1, the same as any number. You cannot distinguish dates from numbers with TYPE alone. If you need to verify a column contains dates, check the cell formatting instead or use ISDATE() if available in your spreadsheet application.
Why does TYPE return 1 for both numbers and dates?: Spreadsheets store dates as serial numbers (the count of days since January 1, 1900, for example). TYPE detects the underlying numeric storage, not the display format. The date '2026-08-20' is stored as a number, so TYPE correctly returns 1. The calendar display is just formatting applied on top.
Can I use TYPE to detect empty cells?: No. TYPE returns 1 for an empty cell (treating it as zero), so you cannot distinguish blank from filled cells using TYPE. Use ISBLANK(cell) to check if a cell is empty; use TYPE for checking whether non-empty cells contain numbers, text, or errors.
What's the difference between TYPE(value)=16 and ISERROR(value)?: Both detect errors, but ISERROR returns TRUE/FALSE (simpler logic), while TYPE returns 16 as a numeric code. For most error handling, use ISERROR or IFERROR. Use TYPE only if you need the type code for complex branching that distinguishes between multiple type categories.

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