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

Thread: Adding/subtracting dates and the '#NUM!" remark

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

    Default

    I need to add or subtract a time period (2 months, for example) to or from a date in another cell. If the other cell is blank, I receive a "1900" date in the new cell when adding; and a "NUM!" remark when subtracting.
    If my formula for B17 is
    =DATE(YEAR(B39),MONTH(B39)-2,DAY(B39))
    and B39 is as yet blank, is there a way to hide the "NUM!" remark until B39 has some information entered?

    Thanks for any assistance!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,021
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-26 12:52, BlueskiesChicago wrote:
    I need to add or subtract a time period (2 months, for example) to or from a date in another cell. If the other cell is blank, I receive a "1900" date in the new cell when adding; and a "NUM!" remark when subtracting.
    If my formula for B17 is
    =DATE(YEAR(B39),MONTH(B39)-2,DAY(B39))
    and B39 is as yet blank, is there a way to hide the "NUM!" remark until B39 has some information entered?

    Thanks for any assistance!
    Try:

    =IF(B39,DATE(YEAR(B39),MONTH(B39)-2,DAY(B39)),"")

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could use something like this:

    =IF(ERROR.TYPE(DATE(YEAR(B39),MONTH(B39)-2,DAY(B39))) = 6, "No Date Yet", DATE(YEAR(B39),MONTH(B39)-2,DAY(B39)))

    but Aladin or maybe even Chris Davison will give a more concise formula.

    c.f. http://www.mrexcel.com/board/viewtop...5023&forum=2&4

    EDIT:: See? Told you, he even hit Submit just before me, whatta guy.
    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-26 12:58 ]

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

    Default

    That was fast! Thanks - worked like a charm. Thanks again!

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
  •