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

**buddyhackit9** · 12-11-2011, 11:38 PM

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!

**dilipandey** · 12-11-2011, 11:45 PM

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 >

**buddyhackit9** · 12-12-2011, 12:57 AM

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.

**buddyhackit9** · 12-12-2011, 01:03 AM

Figured it out. Thanks!!

Used:

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

**dilipandey** · 12-12-2011, 01:17 AM

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 >