Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Date subtraction and IF()

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    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 ?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •