View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Sloth Sloth is offline
external usenet poster
 
Posts: 252
Default 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