Calculating to add years - including leap year

Calculating to add years - including leap year

Post by TWFyZXNraU » Fri, 20 Jul 2007 02:42:13


I have a function that needs to add 3 years to a date field. I am currently
adding 1095 days (3 years x 365) to the base number, but if there is a leap
year in those three, I'm off by one day. How can I add three years and make
sure that the extra day is included if one year is a leap year?
 
 
 

Calculating to add years - including leap year

Post by Peo Sjoblo » Fri, 20 Jul 2007 02:58:02

One possible way

=DATE(YEAR(F1)+3,MONTH(F1),DAY(F1))


where F1 holds the date you want to add 3 years to

Note that if the date in F1 is for instance 02/29/08 then my formula will
return
03/01/11


--
Regards,

Peo Sjoblom

 
 
 

Calculating to add years - including leap year

Post by TWFyZXNraU » Fri, 20 Jul 2007 05:26:09

Other users enter a date in a given cell, and I have another cell that should
calculate three years out from that date. I can't just add 1095 days... how
can I write the function to add 3 years to a different date field? I need to
get your formula below to recognize that the date for (F1) comes from another
cell.
 
 
 

Calculating to add years - including leap year

Post by Peo Sjoblo » Fri, 20 Jul 2007 05:31:03

That's what it does

If you put a date in F1 (in my example) and format the cell with the formula
as a date you will get a date with 3 years added and it will take leap years
into consideration


--
Regards,

Peo Sjoblom
 
 
 

Calculating to add years - including leap year

Post by VGVldGhsZX » Fri, 20 Jul 2007 08:50:02

Try this:
=EDATE(A1,12*3)

Required Analysis ToolPak Add-Ins