Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

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?


Re: Adding dates

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

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

=IF(C1="y",DATE(YEAR(A1),MONTH(A1)+12,DAY(A1)))

Celia


Re: Adding dates

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

PS
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.


Re: Adding dates

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)

Ivan



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.