B

#### BAD

service equals to 75. I have the birth date and the date of hire but have no

idea where to begin. Any suggestions?

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

B

service equals to 75. I have the birth date and the date of hire but have no

idea where to begin. Any suggestions?

S

Try this

=DATEDIF(A1,TODAY(),"y")+DATEDIF(A2,TODAY(),"Y")

in A1 enter the birthdate, in A2 the date of hire.

If this helps click the Yes button.

L

date in column C and row 1 containing headers, in column D row 2:

=(2*Today()-B2-C2)/365.25

gives you what you are looking for.

M

There must be a formula bit I can't see it. Until someone comes up with one

it can be resolved with Goal seek

DOB in A1

DES in A2

Any date you want in A3

This formula in B1 =DATEDIF($A$1,A3,"y")

This formula in B2 =DATEDIF($A$2,A3,"y")

This formula in B3 =Sum(B1:B2)

Select B3 then

Tools|Goal seek

In the 'To value box' enter 75

In the 'By changing' box enter A3

OK and you get your retirement date in B3

Mike

G

BAD said:

service equals to 75. I have the birth date and the date of hire but have no

idea where to begin. Any suggestions?

Birth date in A1, hire date in A2

=(27394-(A2-A1))/2+A2

G

Glenn said:Birth date in A1, hire date in A2

=(27394-(A2-A1))/2+A2

Don't forget to format the result as a date.

B

This gives me a date of march/1900. It should be a future date.

B

same deal here...This gives me a date of march/1900. It should be a future

date.

date.

B

In A3, you say "put any date you want". What date am I supposed to use here?

L

Err... I guess it would help if I did the algebra on that.

=TEXT((27393.75+B2+C2)/2,"mm/dd/yyyy")

=TEXT((27393.75+B2+C2)/2,"mm/dd/yyyy")

M

Maybe you should do as i suggested and put 'Any date' you desire.

Excel will change that date when you run goal seek, It just requires the

cell to have a date in to get it started

Mike

B

Thanks but I am still about a year off using your formula.

L

That's interesting. What combination of values did not produce the expected

result?

result?

B

little off by a couple months. Thanks though.

B

My bday is 10/01/80. My hire date is 06/05/06. With the rule of 75 I

should be eligible to retire on June 4, 2031 (On that date, I will complete

25 yrs of service and will be 50 yrs old). Your formula is giving me the

date 02/01/2031. Unless I am doing something wrong???? Thanks again for

your help

L

of Employment = 75 years.

With a birthdate of 10/1/80 and a hire date of 6/5/06, on 2/1/2031 you would

be 50.33 (50.36 if you don't figure in the leap day) years old and you would

have worked for the company 24.66 years (plus a bit extra in both cases,

which puts you at 74.99 (and a bit) total age+length of employment. If you

add one day ( =TEXT((27393.75+A2+B2)/2+1,"mm/dd/yyyy") ), you're over 75

years (age+employment).

On June 4, you will be 50.67 years old, and will have worked 24.996 years at

the company.

Seems to me to be a rounding issue. Likely the case with the other formulas

as well.

If this doesn't work for you I can try to make this less accurate?

R

retirement date must be the workday that is one day earlier than your hire

date? I ask because there is no way June 4, 2031 can be calculated as

exactly 75 years total from your two dates. Consider that there are 18508

days between that retirement date and your birthday and that there are 9130

days between that retirement date and your hire date. The total is 27638

days and when divided by 366 (assumes every year is a Leap Year in order to

produce the smallest possible number of calculated years) equals 75.5136612.

So in order of June 4, 2031 to meet your 75 year criteria, there must be

another rule at work here... or you calculated your retirement date

incorrectly.

M

dates. i.e. 2/2/2031-10/1/1980 will equal 18386 days (50.3..yrs) and then

2/2/2031-6/5/2006 will equal 9008 days.(24.6...yrs) If using 365.25 to

convert it to years then the answer is 75.00068 the first day you go over

75. So my answer is 2/2/2031.

S

Assume DOB is in B1 and Date of Hire is in C1

(((75*365.25)-(C1-B1))/2)+C1

Taking your years needed and multiplying by 365.25 to get number of days.

You can just use 27393.75, but wanted to show my work...

Take the date of hire and subtract the date of birth from it. This will give

number of days from birth to hire. Take that amount out of your 75 year

calculation. This now normalizes your calculation.

Divide by 2 since we are double counting every day, one for your aging, one

for your working. This will give your number of days from hire to reach your

75 year goal.

Now, just add all these days to your date of hire to show date of goal.

Maybe confusing, but it works.

G

Sean said:

Assume DOB is in B1 and Date of Hire is in C1

(((75*365.25)-(C1-B1))/2)+C1

Taking your years needed and multiplying by 365.25 to get number of days.

You can just use 27393.75, but wanted to show my work...

Take the date of hire and subtract the date of birth from it. This will give

number of days from birth to hire. Take that amount out of your 75 year

calculation. This now normalizes your calculation.

Divide by 2 since we are double counting every day, one for your aging, one

for your working. This will give your number of days from hire to reach your

75 year goal.

Now, just add all these days to your date of hire to show date of goal.

Maybe confusing, but it works.

This is virtually the same as what I posted almost three and a half hours

ago...the OP responded to everyone with the "wrong" answer and not to the

"right" answer (assuming you and I are "right").

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.