Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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"). |
#18
|
|||
|
|||
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:
__________________
I am not human. I am an Excel Wizard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy from Fidelity Retirement Income Planner? | Excel Discussion (Misc queries) | |||
How to copy from Fidelity Retirement Income Planner? | Excel Discussion (Misc queries) | |||
How do i set up a sheet to count the years to retirement? | Excel Worksheet Functions | |||
I need to know formula for matching retirement percents | Excel Worksheet Functions | |||
NPER - Retirement Example | Excel Worksheet Functions |