Trying to calculate retention requirements. Cell A1=6/18/09 and cell A2=7/18/11. If A1 + 3 years is greater than A2+1 year, the result should be A2 + 3 years, if not, then A2 + 1 year. So comparing the 2 dates A1=6/18/09 + 3 years is 6/18/12 and A2=7/18/11 + 1 year is 7/18/12, then I want the later of the dates to be entered. In this case, 7/18/12.

Is there a difference between A1 greater than 2 years less than A2 for your comparison? Assuming 365 days a year

=IF(A1+3*365>A2+365,A2+3*365,A2+365)

=IF(DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))>DATE(YEAR(A2)+3,MONTH(A2),DAY(A2)),DATE(YEAR(A2)+3,MONTH(A2),DAY(A2)),DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)))

Not sure how you want to deal with leap years/leapday so I didn't bother to try an include anything for that

Ask Your Question

Weekly Poll