Struggling with date formulas
It is easier to break your problem in to multiple cells like this
DOB DOH DOB+21 DOH+1 MAX DOE
5/1/1960 1/1/2007 5/1/1981 1/1/2008 1/1/2008 7/1/2008
C2: =DATE(YEAR(A2)+21,MONTH(A2),DAY(A2))
D2: =DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))
E2: =MAX(C2,D2)
F2: =DATE(YEAR(E2)+(MONTH(E2)=7),6*(MONTH(E2)<7)+1,1)
You "can" use one formula with a little copy and pasting, but it is rather
confusing and long.
=DATE(YEAR(MAX(DATE(YEAR(A2)+21,MONTH(A2),DAY(A2)) ,DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))))+(MONTH(MAX(D ATE(YEAR(A2)+21,MONTH(A2),DAY(A2)),DATE(YEAR(B2)+1 ,MONTH(B2),DAY(B2))))=7),6*(MONTH(MAX(DATE(YEAR(A 2)+21,MONTH(A2),DAY(A2)),DATE(YEAR(B2)+1,MONTH(B2) ,DAY(B2))))<7)+1,1)
Basically you need to take the max of the 21st birthday, and 1 year
anniversery. Then, if the month is less than 7, make it 7, otherwise make
the month 1 and add 1 to the year.
Sloth Feb 2008
|