Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / February 2008

Tip: Looking for answers? Try searching our database.

Struggling with date formulas

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bonnie A - 19 Feb 2008 13:56 GMT
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.

Signature

Bonnie W. Anderson
Cincinnati, OH

Sloth - 19 Feb 2008 17:26 GMT
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(DATE(YEAR(A2)+21,MONTH(A2),DAY(A2)),DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))))>=7),6*(MONTH(MAX(DATE(YEAR(A2)+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
Bonnie A - 20 Feb 2008 12:52 GMT
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`
Signature

Bonnie W. Anderson
Cincinnati, OH

> It is easier to break your problem in to multiple cells like this
>
[quoted text clipped - 16 lines]
>
> Sloth Feb 2008
Bonnie A - 20 Feb 2008 13:22 GMT
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...
Signature

Bonnie W. Anderson
Cincinnati, OH

> It is easier to break your problem in to multiple cells like this
>
[quoted text clipped - 16 lines]
>
> Sloth Feb 2008
Bonnie A - 20 Feb 2008 17:30 GMT
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`
Signature

Bonnie W. Anderson
Cincinnati, OH

> Hi again Sloth,
>
[quoted text clipped - 34 lines]
> >
> > Sloth Feb 2008
Tyro - 20 Feb 2008 00:24 GMT
See http://www.cpearson.com/excel/datedif.aspx

Tyro

> Hi everyone.  Using E02 on XP.  I am trying to work some formulas and I
> have
[quoted text clipped - 19 lines]
> of
> what I need.  Thanks in advance for your time.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.