Calculating yearly start dates ignoring leap years

**Steve S.** · 02-25-2015, 10:25 AM

I am trying to calculate the start date for option years on a contract that could have anywhere from 1 to 4 option years. I have blocks in my worksheet representing the base contract year start date (A1), base contract year end date (A2) and the final contract year end date (A3). My 1st option year start date formula is =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1) and this works great. My second year start date formula is =DATE(YEAR(A1)+2,MONTH(A1),DAY(A1) but if the result of this formula is greater than the final year end date I want the block to show N/A and I cannot figure out how to do this.

**azumi** · 02-25-2015, 03:36 PM

Can you show what you're trying to do with sample workbook? To upload click "Go Advanced" button and find paperclip button to attach the file.

regards

**daddylonglegs** · 02-25-2015, 03:50 PM

You could possibly shorten your formulas by using EDATE, e.g. to get a date one year later than A1 you can use this formula

=EDATE(A1,12)

If you want to show N/A when that's later than A3 you can use this version

=IF(EDATE(A1,12)>A3,"N/A",EDATE(A1,12))

For 2 years just use 24 in place of 12