The function ISNUMBER alone won’t help us to extract numbers excluding dates from a range in Google Sheets. Do you know the reason?

The ISNUMBER function would return TRUE if the cell that tested using this function contains a number or date.

This poses a challenge in extracting numbers excluding dates from a cell or range in Google Sheets.

Another two functions that we can try as alternatives are TYPE and N. Both of these functions have the same issue.

The function TYPE would return 1 for both numbers and dates and 2 for text. Also, we can’t use it in a range.

On the other hand, the N function would return date value if the tested cell contains a date, the same number if the cell contains a number, else 0.

So what is the solution to extract numbers excluding dates from a range in Google Sheets? I mean how to differentiate a number from a date using a formula in Google Sheets?

As a side note, if you want to extract both numbers and dates (both are numbers in Google Sheets) from a range then please follow this guide – How to Copy Only Numbers from Multiple Columns in Google Sheets.

How to Exclude Dates While Copying Numbers in Google Sheets (Formula)

To copy/extract numbers that excluding dates (since date are numbers) we can use a ‘key’ combination of ISNUMBER and DATEVALUE functions.

You will get that formula in a few steps below so that I can avoid the formula explanation part. Here we go!

Different Value Types in the ISNUMBER Function

To test whether a cell, for example, cell A1, contains a number or date, not a text string, we can use the formula given below.

Formula # 1:

=isnumber(A1)

The Analysis of the ISNUMBER Formula # 1 Result:

• If A1 = Number, the result would be TRUE.
• A1 = Text, the result would be FALSE.
• A1 = Date, the result would be TRUE.

Now to the second key function in the combination.

Different Value Types in the DATEVALUE Function

As far as I know, there is no function capable to test whether a cell contains only a number, not a date or text. But luckily we have a function that is capable of identifying dates in a cell. That function is DATEVALUE.

I know there is ISDATE too but it won’t suit our purpose. Why?

We want to extract numbers excluding dates from a range not from a single cell in Google Sheets.

The ISDATE won’t return a range, on the contrary, the DATEVALUE will do. So there is no point in using ISDATE in our combination formula.

The said DATEVALUE function would return #VALUE! error if the tested cell doesn’t contain a date.

That means, by wrapping it with the popular error removing function IFERROR, we can get null in case of the cell contains a Text or Number.

Now see the formula given below.

Formula # 2:

=iferror(DATEVALUE(A1)>0)

The Analysis of the DATEVALUE Formula # 2 Result:

• If A1 = Number, the result would be blank.
• A1 = Text, the result would be blank.
• A1 = Date, the result would be TRUE.

ISNUMBER and DATEVALUE Combo to Extract Numbers Excluding Dates in Google Sheets

From the above two formulas, we can come to the below conclusion.

• If the formula # 1 output + formula # 2 output = 1, the cell in question contains a number.
• Formula # 1 output + formula # 2 output = 0, cell contains a text.
• Formula # 1 output + formula # 2 output = 2, cell contains a date

Note: Boolean FALSE is 0 and TRUE is 1.

The below illustration would help you understand the combination of formula 1 and formula 2 better.

As per the above, to extract value from cell A1, if it’s a number, not a date or text, we can use the formula given below.

Sheets Formula for Differentiating Date and Number (Copying Numeric Value):

=if(
     isnumber(A1)+
     iferror(DATEVALUE(A1)>0)
     =1,A1,
)

The IF logical function returns the value from cell A1, if the combination result is 1, else blank.

My values are in A1:C10. To extract numbers excluding dates as well as text strings from a range use the array version of the above formula.

=ArrayFormula(
     if(
        isnumber(A1:C10)+
        iferror(DATEVALUE(A1:C10)>0)
        =1,A1:C10,
     )
)

If the formula is in ‘Sheet2’ and the values are in ‘Sheet1’, then A1:C10 must be replaced by Sheet1!A1:C10.

Formula to Flatten Extracted Numbers that Excluding Dates from a Range

The above range A1:C10 contains 3 columns and several blank cells. Using the FLATTEN function, we can flatten the values to a single column.

=flatten(
     ArrayFormula(
        if(
           isnumber(A1:C10)+
           iferror(DATEVALUE(A1:C10)>0)
           =1,A1:C10,
        )
     )
)

Finally use the QUERY to remove the blank cells in the flattened column.

=query(
     flatten(
        ArrayFormula(
           if(isnumber(A1:C10)+iferror(DATEVALUE(A1:C10)>0)=1,A1:C10,)
        )
      ),
     "Select * where Col1 is not null"
)

That’s all about extracting numbers excluding dates from a range in Google Sheets.