+ Reply to Thread
Results 1 to 5 of 5

Adding years to a date, but leaving blank if no date in the original cell.

  1. #1
    Registered User
    Join Date
    12-11-2011
    Location
    Penticton, BC
    MS-Off Ver
    Excel 2010
    Posts
    9

    Adding years to a date, but leaving blank if no date in the original cell.

    Hi, I have the following formula to add 5 years to a date:
    =DATE(YEAR(P5)+5,MONTH(P5),DAY(P5))

    The problem I am having is that if "P5" is blank, then my cell is defaulting to 31/12/1904 for some reason. I would like it to just stay blank ideally.

    Any suggestion?

    Thanks in advance!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Adding years to a date, but leaving blank if no date in the original cell.

    Hi buddy,

    Use following formula to handle blanks:-

    Please Login or Register  to view this content.
    Hope this solves your problem.

    Regards,
    DILIPandey

    < click on below 'star' if this helps >
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    12-11-2011
    Location
    Penticton, BC
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Adding years to a date, but leaving blank if no date in the original cell.

    That worked great. Unfortunately when I changed that formula, it affected my other formula which was:

    =IF(OR(R5>$O$1),"Up to date", "SITE VISIT NEEDED")

    Now everything shows up as "Up to date" because R5 is blank as per the formula you helped me with earlier. I think I need another variation of the ISBLANK formula, but I tried it and can't get success so far.

    Thanks.

  4. #4
    Registered User
    Join Date
    12-11-2011
    Location
    Penticton, BC
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Adding years to a date, but leaving blank if no date in the original cell.

    Figured it out. Thanks!!

    Used:

    =IF(R5="","SITE VISIT NEEDED",IF(OR(R5>$O$1),"Up to date"))

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Adding years to a date, but leaving blank if no date in the original cell.

    Great.. I was also thinking on the similar line..
    There is some scope for improvement as well . . like using OR at end with only one criteria and providing no value for false condition.

    Let the forum know if you encounter any error so that necessary help can be provided.

    Regards,
    DILIPandey

    < click on below 'star' if this helps >

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1