Hi again Sloth,

If you have it in you for me to bug you one more time...

If I had Age {21} typed in cell G1 and Service {1} typed in cell J1, how can
I replace them in your long formula? I tried but cannot make G1 stay G1
(when I copy down it changes to G2 and G3, etc. (Same with J1.) I thought a
$ sign would hold it but no.

The reason I ask is I realized that not all plans have the same age and
service requirements. It might be age 25 and 3 years of service.

I think I adjusted all the 21's to G1's but wasn't sure which of the 1's to
change to J1's after the first 3. It seems to work on the row I edit but
then, my formula doesn't work when I copy it down.

Thank you very much for your time. I'll cross my fingers...
--
Bonnie W. Anderson
Cincinnati, OH


"Sloth" wrote:

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