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

Question

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

Reply

Answer 1

May 3, 2014 5:44 PM in response to iamzoran

That's an interesting expression. I can't say why you get the error for sure, but I can say that you should be able to write:

=IF(A11>0,D11−C11,"") and get the same result when all your data is properly formatted.

Using SUM with a single cell's value adds nothing, and neither does enclosing the subtraction in parentheses.

Numbers differs a lot from Excel in the Time department. Numbers has a Date/Time value type, which is a precise point in time on a precise date. There is no time of day value. Numbers also has a Duration value type. The result of subtracting two Date/Time values is a Duration value. If you enter only a time of day, the current date is assumed and becomes part of the value.

Jerry

Reply

Answer 2

May 4, 2014 12:45 AM in response to iamzoran

I don't see why a sum formula would be used for just one cell and I don't see why times would be entered if no date was entered for a time sheet. Therefore this idea may or may not work for you.

Reply

Answer 3

t quinn

Level 5

7,063 points

May 4, 2014 8:02 AM in response to iamzoran

Hi Zoran,

I think the issue is with the expression ">0"

I can escape the error if I do this:

Maybe someone has a better way.

quinn

Reply

Answer 4

t quinn

Level 5

7,063 points

May 4, 2014 8:22 AM in response to t quinn

If you are just testing for an entry how about:

IF(ISBLANK(A11),"",D11−C11)

q

Reply

Answer 5

iamzoran Author

Level 1

9 points

May 4, 2014 1:26 PM in response to t quinn

Bingo!

IF(ISBLANK(A11),"",D11−C11) this did it.

Reply

Answer 6

iamzoran Author

Level 1

9 points

May 4, 2014 1:31 PM in response to Listen Inn

Listen Inn,

Your suggestion worked also: IF(A19≠"",D19−C19,"")

Reply

Answer 7

May 4, 2014 1:32 PM in response to iamzoran

Another way to check for lack of entry is to check the character length of the cell. So, you could also write:

IF(LEN(A)<1, "", D-C)

Jerry

Reply

Answer 8

iamzoran Author

Level 1

9 points

May 4, 2014 1:47 PM in response to Jerrold Green1

Jerrold Green1,

I agree with your words about using Sum with a single cell [have no idea why the "creator" of the file did that]; same for the parentheses enclosing subtraction. Yet cleaning it up as you showed me [=IF(A11>0,D11−C11,"") ] I got the same comment

"You can’t compare a date with a number because their data types are different."

Makes me think that t quinn is on to something with his "think the issue is with the expression ">0". Could it be that >0 is the number referred to and <>"" and ISBLANK are not numbers?!

z

PS: I am so happy that I am able to use Spelling and Grammar Checker with Safari on these posts. I was born and raise in old Yugoslavia and I use the Checker with all my emails etc and its a life saver. hahaha

Reply

Answer 9

May 4, 2014 2:04 PM in response to iamzoran

I don't believe that you ever said what the "creator" has in column A.

Jerry

Reply

Answer 10

iamzoran Author

Level 1

9 points

May 4, 2014 2:31 PM in response to iamzoran

Jerry,

Thank you. IF(LEN(A)<1, "", D-C) worked. So I ask myself, what does all this mean? 3 solutions. I see the simplicity of your formula but I am not sure I am cut out for it, for any of this. i understand it strategically, but when I get on the tactical level I am lost. So what I have learned from this wonderful discussion, I should not be doing this.

I need help, but not to learn, but to have someone who is talented in this area to do it for me. I prototype using spreadsheets but my work product is too crude for use by others. Is this a another question or is it not a question for this type of forum in the first place. If so where do I go to find help?

Appreciate some guidance,

z

Reply

Answer 11

iamzoran Author

Level 1

9 points

May 4, 2014 2:40 PM in response to Jerrold Green1

I don't believe that you ever said what the "creator" has in column A.

Jerry

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

Reply

Answer 12

May 4, 2014 2:43 PM in response to iamzoran

Z,

Let's get rid of those blue flags. What do they say when you hover over them?

Jerry

Reply

Answer 13

iamzoran Author

Level 1

9 points

May 4, 2014 2:49 PM in response to Jerrold Green1

Reply

Answer 14

May 4, 2014 2:55 PM in response to iamzoran

Substitute the following formula in B.

=LEFT(DAYNAME(A),3)

Jerry

Reply

Answer 15

iamzoran Author

Level 1

9 points

May 4, 2014 2:57 PM in response to iamzoran

Awesome!

Jerry,

Here is the last issue, please. Formula that multiplies the rate time duration for a Total amount. It suffers from the same malady of comparing date and number.

Reply