Rule of 75 Retirement Calculation
 

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Answer: Rule of 75 Retirement Calculation
 

The Rule of 75 Retirement Calculation is a common method used to determine when someone is eligible for retirement benefits. To calculate the retirement date, follow these steps:

1. Enter the birth date of the person in one cell, let's say A1. Make sure the date is formatted as a date and not as text.
2. In another cell, let's say B1, enter the date of hire of the person. Again, make sure the date is formatted as a date.
3. To calculate the years of service, subtract the date of hire from today's date. You can use the following formula in another cell, let's say C1:
   
   Formula:

   ---

   =DATEDIF(B1,TODAY(),"y") 


   ---

4. Now, use the following formula to calculate the retirement date. Replace "Birthdate" with the cell reference of the birth date (A1 in our example) and "Years of Service" with the cell reference of the years of service (C1 in our example):
   
   Formula:

   ---

   =DATE(YEAR(A1)+75-C1,MONTH(A1),DAY(A1)) 


   ---

Rule of 75 Retirement Calculation
 

Hi,

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.
--
Thanks,
Shane Devenshire


"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

I think with the person's name in column A, birthdate in column B, and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

Hi,

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




"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

BAD wrote:
I need a formula to give me the date of when someone's age and years of
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

Rule of 75 Retirement Calculation
 

Glenn wrote:
BAD wrote:
I need a formula to give me the date of when someone's age and years
of 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


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

Rule of 75 Retirement Calculation
 

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

"ShaneDevenshire" wrote:

Hi,

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.
--
Thanks,
Shane Devenshire


"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B, and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

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

"Mike H" wrote:

Hi,

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




"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

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

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B, and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

Hi,

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

"BAD" wrote:

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

"Mike H" wrote:

Hi,

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




"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

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

"~L" wrote:

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

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B, and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

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

"BAD" wrote:

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

"~L" wrote:

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

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B, and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

Thanks, I see how it works. It doesn't give me the correct date. It's a
little off by a couple months. Thanks though.

"Mike H" wrote:

Hi,

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

"BAD" wrote:

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

"Mike H" wrote:

Hi,

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




"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

I'm sorry it says its about 4 months off.

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" wrote:

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

"BAD" wrote:

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

"~L" wrote:

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

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B, and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

Maybe I'm not understanding the Rule of 75. I took it to mean You Age + Leng
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?

"BAD" wrote:

I'm sorry it says its about 4 months off.

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" wrote:

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

"BAD" wrote:

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

"~L" wrote:

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

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B, and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

Is there an additional rule that you haven't told us; specifically, that the
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.

--
Rick (MVP - Excel)


"BAD" wrote in message
...
I'm sorry it says its about 4 months off.

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" wrote:

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

"BAD" wrote:

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

"~L" wrote:

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

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B,
and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and
years of
service equals to 75. I have the birth date and the date of hire
but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

As a simple way to check an answer on this, you can simply subtract the two
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.


"Rick Rothstein" wrote in message
...
Is there an additional rule that you haven't told us; specifically, that
the 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.

--
Rick (MVP - Excel)


"BAD" wrote in message
...
I'm sorry it says its about 4 months off.

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" wrote:

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

"BAD" wrote:

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

"~L" wrote:

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

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B,
and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and
years of
service equals to 75. I have the birth date and the date of
hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

Here is your answer.

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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

Sean Timmons wrote:
Here is your answer.

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").

Rule of 75 Retirement Calculation
 

Sorry! You are right! This formula works! Thank you so much!

"~L" wrote:

Maybe I'm not understanding the Rule of 75. I took it to mean You Age + Leng
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?

"BAD" wrote:

I'm sorry it says its about 4 months off.

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" wrote:

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

"BAD" wrote:

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

"~L" wrote:

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

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B, and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

My boss wants to know....

"how about if one of the factors ... years of service ... must be at least 8
years?
The rule is: Age plus years of service equal 75, with a minimum of 8 years
of service, i.e. a new hire who's 75 years old would not automatically be
eligible to retire on their hire date.

Do you any ideas on how to allow for additional years/days of service
elsewhere to count toward the service criteria, i.e. 7 years with the
ccompany plus 1 previous year with another eligible company would meet the 8
year criteria ... or 6 years with the company plus two one-year stints at two
other companies.

Any help would be appreciated.


"~L" wrote:

Maybe I'm not understanding the Rule of 75. I took it to mean You Age + Leng
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?

"BAD" wrote:

I'm sorry it says its about 4 months off.

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" wrote:

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

"BAD" wrote:

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

"~L" wrote:

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

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

"~L" wrote:

I think with the person's name in column A, birthdate in column B, and hire
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.

"BAD" wrote:

I need a formula to give me the date of when someone's age and years of
service equals to 75. I have the birth date and the date of hire but have no
idea where to begin. Any suggestions?

Rule of 75 Retirement Calculation
 

The simple answer is just wrap an IF around it. 8 years should be 2922
days, and I hope I grabbed the proper formula from this discussion, I
haven't tested it completely:

=IF(TODAY()-C2<2922,"Not Eligible",TEXT((27393.75+B2+C2)/2,"mm/dd/
yyyy"))

But for all the "another eligible company" parts, it gets more
complex. You'd have to have that data somewhere in the worksheet to
reference. No doubt it can be done, if those other dates are there.
So...what do you have?

Rule of 75 Retirement Calculation
 

Thanks Spiky. I tested with your formula but I got the "not eligible" text
in my field. Which is correct but I want the formula to tell me the date I
will be eligible.

"Spiky" wrote:

The simple answer is just wrap an IF around it. 8 years should be 2922
days, and I hope I grabbed the proper formula from this discussion, I
haven't tested it completely:

=IF(TODAY()-C2<2922,"Not Eligible",TEXT((27393.75+B2+C2)/2,"mm/dd/
yyyy"))

But for all the "another eligible company" parts, it gets more
complex. You'd have to have that data somewhere in the worksheet to
reference. No doubt it can be done, if those other dates are there.
So...what do you have?

Rule of 75 Retirement Calculation
 

I also have the date when the employee will complete 8 yrs of service if that
helps at all.

"Spiky" wrote:

The simple answer is just wrap an IF around it. 8 years should be 2922
days, and I hope I grabbed the proper formula from this discussion, I
haven't tested it completely:

=IF(TODAY()-C2<2922,"Not Eligible",TEXT((27393.75+B2+C2)/2,"mm/dd/
yyyy"))

But for all the "another eligible company" parts, it gets more
complex. You'd have to have that data somewhere in the worksheet to
reference. No doubt it can be done, if those other dates are there.
So...what do you have?

Rule of 75 Retirement Calculation
 

To build on Spiky's formula:

=IF(TODAY()-C2<2922,"Not eligible prior to "&C2+(365.25*8)
,TEXT((27393.75+B2+C2)/2,"mm/dd/yyyy"))

"BAD" wrote:

Thanks Spiky. I tested with your formula but I got the "not eligible" text
in my field. Which is correct but I want the formula to tell me the date I
will be eligible.

"Spiky" wrote:

The simple answer is just wrap an IF around it. 8 years should be 2922
days, and I hope I grabbed the proper formula from this discussion, I
haven't tested it completely:

=IF(TODAY()-C2<2922,"Not Eligible",TEXT((27393.75+B2+C2)/2,"mm/dd/
yyyy"))

But for all the "another eligible company" parts, it gets more
complex. You'd have to have that data somewhere in the worksheet to
reference. No doubt it can be done, if those other dates are there.
So...what do you have?

Rule of 75 Retirement Calculation
 

Hi ~L,

Your formula gives me Not Elibile prior to 41795. How do you turn that into
a date?

"~L" wrote:

To build on Spiky's formula:

=IF(TODAY()-C2<2922,"Not eligible prior to "&C2+(365.25*8)
,TEXT((27393.75+B2+C2)/2,"mm/dd/yyyy"))

"BAD" wrote:

Thanks Spiky. I tested with your formula but I got the "not eligible" text
in my field. Which is correct but I want the formula to tell me the date I
will be eligible.

"Spiky" wrote:

The simple answer is just wrap an IF around it. 8 years should be 2922
days, and I hope I grabbed the proper formula from this discussion, I
haven't tested it completely:

=IF(TODAY()-C2<2922,"Not Eligible",TEXT((27393.75+B2+C2)/2,"mm/dd/
yyyy"))

But for all the "another eligible company" parts, it gets more
complex. You'd have to have that data somewhere in the worksheet to
reference. No doubt it can be done, if those other dates are there.
So...what do you have?

Rule of 75 Retirement Calculation
 

Format the cell as a date (probably the best way) or:
=IF(TODAY()-C2<2922,"Not eligible prior to
"&TEXT(C2+(365.25*8),"mm/dd/yyyy"),TEXT((27393.75+B2+C2)/2,"mm/dd/yyyy"))

"BAD" wrote:

Hi ~L,

Your formula gives me Not Elibile prior to 41795. How do you turn that into
a date?

"~L" wrote:

To build on Spiky's formula:

=IF(TODAY()-C2<2922,"Not eligible prior to "&C2+(365.25*8)
,TEXT((27393.75+B2+C2)/2,"mm/dd/yyyy"))

"BAD" wrote:

Thanks Spiky. I tested with your formula but I got the "not eligible" text
in my field. Which is correct but I want the formula to tell me the date I
will be eligible.

"Spiky" wrote:

The simple answer is just wrap an IF around it. 8 years should be 2922
days, and I hope I grabbed the proper formula from this discussion, I
haven't tested it completely:

=IF(TODAY()-C2<2922,"Not Eligible",TEXT((27393.75+B2+C2)/2,"mm/dd/
yyyy"))

But for all the "another eligible company" parts, it gets more
complex. You'd have to have that data somewhere in the worksheet to
reference. No doubt it can be done, if those other dates are there.
So...what do you have?