return 0 if cell has data?

K

kathysonisland

I feel stupid asking this question.......I just cannot figure a formula out
that I know I know.........

I need a formula to display 0 in a cell if another cell has a date.
 
D

Dulanic

I feel stupid asking this question.......I just cannot figure a formul
out
that I know I know.........

I need a formula to display 0 in a cell if another cell has a date.



Can the cell only be a date? If so... =IF(E3>0,0,"")
 
J

Joe User

kathysonisland said:
I need a formula to display 0 in a cell if another cell has a date.

Is the following sufficient?

=if(isnumber(A1), 0, "")

That returns zero if A1 ("the other cell") has a number, which includes a
date. Otherwise, it returns the null string, leaving the cell appear blank.

If you truly need to distinguish between a date and other numbers, I think
you need to write a UDF (VBA function). Is that necessary?

Also, I ass-u-me that "the other cell" contains a numeric date, not text
that looks like a date. In other words, TYPE(A1) is 1, not 2.
 
K

kathysonisland

This was what I needed, Thank you! But now it leads me to the next
step.....(probably would be easier to just enter the $0 myself)

Here is what I need: the 'simple' spreadsheet is for refunds we owe
clients. Column D is refund owing, Column G is date mailed, H represents $0
balance, which is the formula I was just given. Now, how do I fix the
formula to actually display the amount of refund due from Column D IF column
G isn't populated, but if it does have a date mailed then H would display $0?

I should have asked this in the first place. Sorry.
 
J

Joe User

kathysonisland said:
Column D is refund owing, Column G is date mailed
[....] how do I fix the formula to actually display the
amount of refund due from Column D IF column G
isn't populated, but if it does have a date mailed then
H would display $0?

=if(isnumber(G1), 0, D1)


----- original message -----
 
J

Joe User

I said:
=if(isnumber(G1), 0, D1)

By the way, it might be sufficient to write if G1 only contains a date or
nothing:

=if(G1<>"", 0, D1)


----- original message -----

Joe User said:
kathysonisland said:
Column D is refund owing, Column G is date mailed
[....] how do I fix the formula to actually display the
amount of refund due from Column D IF column G
isn't populated, but if it does have a date mailed then
H would display $0?

=if(isnumber(G1), 0, D1)


----- original message -----

kathysonisland said:
This was what I needed, Thank you! But now it leads me to the next
step.....(probably would be easier to just enter the $0 myself)

Here is what I need: the 'simple' spreadsheet is for refunds we owe
clients. Column D is refund owing, Column G is date mailed, H represents $0
balance, which is the formula I was just given. Now, how do I fix the
formula to actually display the amount of refund due from Column D IF column
G isn't populated, but if it does have a date mailed then H would display $0?

I should have asked this in the first place. Sorry.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top