MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Adding dates

Posted by Tim on January 13, 2000 6:41 PM

I am trying to set up a spreadsheet that will change a date based on wether or not a specific task has been completed by adding 6 months to the date. All originating from an original date.

i.e. A1 = 1/15/00 B1 = 7/15/00 C1 = "y" or "n"

I want to increment B1 to 01/15/01 if "y" or keep it the same if "n" I can do it with number of days

i.e. 180 with =if(c1="y", a1+180, a1+365)

but that does not give me the same day (15th).

is there another way to do this?

Posted by Celia on January 13, 2000 8:38 PM


The following formula will increase the date in cell A1 by 12 months :-



Posted by Celia on January 13, 2000 8:54 PM

If the date in A1 is the last day of a month, the formula would need to be more complicated than the one shown above.

Posted by Ivan Moala on January 13, 2000 9:16 PM

Hi Tim try this;
if(C1="y",DATE(YEAR($B1)+1,MONTH($B1), DAY($B1)),B1)