How do I Add or subtract months or years from a date in Excel

[Guest]

Trying to add and subtract years and ormonths to dates and concidering leap
years.
=IF(IF(D2>5,B2-365,IF(D2<3,B2-365,IF(C2/4=INT(C2/4),B2-363,B2-364)))<NOW(),NOW(),IF(C2>5,B2-365,IF(C2<3,B2-365,IF(C2/4=INT(C2/4),B2-363,B2-364))))
is too cryptic!

 

[Niek Otten]

Hi Dug,

Seems you're not using real Excel dates. You should, absolutely!
It looks like D2 is your month, C2 is the year and B2 is the day. Adjust if
I'm wrong.
In E2, put: =date(C2,D2,B2)

To add a year:
=Date(year(E2)+1,month(E2),day(E2))

etc

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Niek Otten]

But of course some issues remain; what do you expect the date plus one year
to be if the original date in a leap year is Feb 29?
A resume about dates (and times) in Excel can be found at

http://www.cpearson.com/excel/datetime.htm#SerialDates

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel