Date subtraction and IF()

[stevenmcheerful]

This does not seem to work

IF( A2 + monitor!B2 >= Today(),calc!V2,#N/A)

I want to take the value in WKS monitor cell B2 (say it is 3) and add it to whatever date is in ActiveSheet.A2

If that adds up being equal to or greater than today's date, I want to pull in a value
from another sheet. If not, display #N/A

instead I am getting a date of 01/13/1900
which is not what I want (calc!V2 is a real number)

What am I doing wrong ?

 

[Mark W.]

Format the cell containing this formula as General or using a number format. It defaulted to a date format because one of your IF function arguments (A2) is formatted as a date.
This message was edited by Mark W. on 2002-04-24 13:54

 

[stevenmcheerful]

Thanks. That does make a big difference.

Here is the thing, though, I am trying to
display a column (for a Chart Series) where
the non-applicable cells are changed to #N/A
where the Date subtraction is done. For ex:

on the worksheet I use to Chart I copy the
date and value from another worksheet, and
I am looking to do this kind of thing:

01/20/2002,2, #N/A, 3.14626,4.28064
01/21/2002,1, #N/A, 2.41421,4.28064
01/22/2002,3, #N/A, 3.73205,4.28064
01/23/2002,#N/A, 3, 3.73205,4.28064
01/24/2002,#N/A, 2, 3.14626,4.28064

where I want to Chart only certain values
in the Series if they are within the last
X days (i.e. recent values).

So my formulas look like this

=IF(A2 + monitor!$B$2 < TODAY(),calc!B2,#N/A)

but as you noted the FORMAT must be GENERAL or NUMBER or the result is this:


3/20/2002 #VALUE! #VALUE! 1
3/21/2002 #VALUE! #VALUE! 1
3/22/2002 #VALUE! #VALUE! 1

The formula will work fine when I format the cells to be "General", but then the
Chart is messed up because I rely on the X axis to be Dates in sequential order. (The chart is bad).

Long story, here's the Q: How do I get my
data Series' setup with #N/A but also
get my chart to show in Date format ?

thanks

- steve