Struggling with date formulas
 

Hi everyone. Using E02 on XP. I am trying to work some formulas and I have
the general idea what I need to do.

I have a date of birth [DOB] and date of hire [DOH]. I need to calculate
Eligibility to Participate by calculating [Age] and [Service] and then: If
[Age]21 AND [Service]1, name the next occuring 01/01 or 07/01 (with the
year).

In other words, once you attain age 21 and have at least 1 year of service,
you are eligible to enroll on the next occuring January 1 or July 1. The
ultimate question is "What is the Date of Eligibility?" If DOB is 05/01/1960
and DOH is 01/01/2007, they are 21 on 05/01/1981 and they have one year of
service on 01/01/2008, resulting in a Date of Eligiblity of 07/01/2008.

I'm working on it but it looks like I'm using a half dozen fields to
calculate each step. I'm sure it can be nested into one formula.

I would appreciate any help or advice, perhaps where to look for examples of
what I need. Thanks in advance for your time.

--
Bonnie W. Anderson
Cincinnati, OH

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

Struggling with date formulas
 

See http://www.cpearson.com/excel/datedif.aspx

Tyro

"Bonnie A" wrote in message
...
Hi everyone. Using E02 on XP. I am trying to work some formulas and I
have
the general idea what I need to do.

I have a date of birth [DOB] and date of hire [DOH]. I need to calculate
Eligibility to Participate by calculating [Age] and [Service] and then: If
[Age]21 AND [Service]1, name the next occuring 01/01 or 07/01 (with the
year).

In other words, once you attain age 21 and have at least 1 year of
service,
you are eligible to enroll on the next occuring January 1 or July 1. The
ultimate question is "What is the Date of Eligibility?" If DOB is
05/01/1960
and DOH is 01/01/2007, they are 21 on 05/01/1981 and they have one year of
service on 01/01/2008, resulting in a Date of Eligiblity of 07/01/2008.

I'm working on it but it looks like I'm using a half dozen fields to
calculate each step. I'm sure it can be nested into one formula.

I would appreciate any help or advice, perhaps where to look for examples
of
what I need. Thanks in advance for your time.

--
Bonnie W. Anderson
Cincinnati, OH

Struggling with date formulas
 

Good morning Sloth,

I have to start by saying that you are absolutely flippin brilliant!!! Thank
you SO very much. I have copy/pasted out both your long way and short way.
I can understand most of the long way but your short way is exactly what I
needed.

I'm gonna hesitate just a few seconds before I admit that I got the formula
from the NewsGroups. You are makin' me look good today.

Seriously, thank you for the time you take to help others. I'm in the
Access line 99% of the time but wanted to help a friend on this project.

Ciao`
--
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

Struggling with date formulas
 

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

Struggling with date formulas
 

Sloth! I figured it out!!! I was trying just one $ sign but I needed $G$1!!!
Same for J1. Thank you again for your help today. Awesome!!!

Ciao`
--
Bonnie W. Anderson
Cincinnati, OH


"Bonnie A" wrote:

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