Level 1

9 points

"You can’t compare a date with a number" in a formula that worked in Excel

I opened in Numbers an invoice file which was created in Excel. Formula returns hours worked. It has an "if" condition for the date field. I am assuming that that is the reason I get this "You can’t compare a date with a number because their data types are different." What do I do?

Formula reads =IF(SUM(A11)>0,(D11−C11),"") where A11 is a date and D11- C11 are times, ie 4PM and 5PM

Message was edited by: iamzoran

MacBook [MacBook5,1] 2.4 GHz, Mac OS X (10.5.6), Number Of Processors: 1 Total Number Of Cores: 2 L2 Cache: 3

Posted on May 3, 2014 1:41 PM

23 replies

iamzoran Author

Level 1

9 points

May 4, 2014 3:09 PM in response to iamzoran

Jerry,

You'd be proud of me. When I pasted the formula for B into the next line that did not have any entries i got this:

So I borrowed from the formula you gave me earlier and eliminated the syntax error. 🙂

May 4, 2014 3:53 PM in response to iamzoran

Nice work!

You are finding that the compatibility of Numbers with Excel depends on the complexity of the Excel document.

Simple data, no problem. Complex formulas, more work for you. It's best to ask not how to fix the Excel expression that is failing, but rather what would I do in Numbers.

Jerry

iamzoran Author

Level 1

9 points

May 5, 2014 3:04 PM in response to Jerrold Green1

Jerrold Green1

Sorry about misspelling your name in my email.

I resolved my last issue I mention above with TOTAL this morning.

Formula was =IF(SUM(A17)>0,(E17*24)*F17,""). I switched from SUM(A17).0 to LEN(A)<1,"" to escape the error message and got rid of 24.

t quinn suggestion worked =IF(ISBLANK(A19),"",E19×F19).

Listen Inn sugested IF(A19≠"",E19×F19,""). It worked also.

So what is the difference between ISBLANK and <>""? I have discovered that LEN is faster with larger iterations, which does not apply here.

Thanks for all your help.

May 5, 2014 4:10 PM in response to iamzoran

There is usually more than one approach to any problem. Which one you choose depends on your knowledge of the data, in some cases. Often there is no material difference.

We were throwing different options your way in hopes that something would work for you. Your original problem statement was lacking in detail, so we were guessing to a great degree.

Jerry

t quinn

Level 5

7,063 points

May 5, 2014 5:35 PM in response to iamzoran

Hi z,

I forget about LEN even though I have seen Jerry offer it for this test in the past. I think it is superior to ISBLANK in many situations because it will test the output of a formula. This is true for Listen Inn's "≠" test also. ISBLANK will return false if the cell has a formula in it even if the formula's result is 0 or "". Since we were testing an input cell ISBLANK worked well.

quinn

iamzoran Author

Level 1

9 points

May 7, 2014 8:01 PM in response to iamzoran

Jerry, quinn, Listen Inn,

You guys are doing a great job and I thank you for taking the time to help people like me.

If you ever finds yourself on Maui, let's meet, dinner's on me. I am easy to contact, just google iamzoran.

Aloha,

Barry

Level 9

60,023 points

May 7, 2014 10:36 PM in response to iamzoran

Hi Zoran,

"A is for date on which the from to work hours were performed."

Which explains the error message you were getting.

A11 contains a Date (and Time) value. '0' is a number. '>' is a comparison operator.

A11>0 compares the date in A11 with the number 0. which is something that cannot be done, so the formula returns the error message "You can't compare a date with a number because their data types are different."

Regarding Jerry's test for content in a cell:

IF(LEN(A)<1,"",do this)

LEN(A) returns the length in characters of the value displayed in the cell on the same row of column A. If the cell is empty, or contains a null string, the length is zero. This is less than one, so the comparison returns TRUE, and the formula inserts a null string ( "" ) in its cell. If the cell contains any value, the display of that valus will take at least one character, the comparison will return FALSE, and the lat part of the formula (do this) will be triggered and return whatever value it is set to calculate.

Regards,

Barry

May 8, 2014 12:52 PM in response to iamzoran

iamzoran wrote:

Jerry, quinn, Listen Inn,

You guys are doing a great job and I thank you for taking the time to help people like me.

If you ever finds yourself on Maui, let's meet, dinner's on me. I am easy to contact, just google iamzoran.

Aloha,

z

That's great. Now I have a good reason to go to Maui.

Jerry

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

"You can’t compare a date with a number" in a formula that worked in Excel