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?

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

=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

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.

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.

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

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

Try this:

=EDATE(A1,12*3)

Required Analysis ToolPak Add-Ins

=EDATE(A1,12*3)

Required Analysis ToolPak Add-Ins

1. Fractional year dates (leap year adjusted)

2. lost exactly 1 year of data - leap year issue?

3. Create dates for year, allow for leap year

4. Mac shipments lowest in 1.5 years - first year-over-year drop in 5.5 years

5. Mac shipments lowest in 1.5 years - first year-over-year drop in 5.5 years

6. Code to calculate a leap year

8. Calculate expirate date taking into account leap year

9. A better algorithm to calculate a leap year?

10. How do I calculate year vs. year sales in a percentage?

11. How to make a calculated field (year-to-year growth rate) in a pivot table?

12. how to calculate average within a year before grouping by year

13. Calculate Year from Current Year

14. OLAP Cube: Calculate Year On Year

15. formula to calculate current year less the year born

5 post • Page:**1** of **1**