If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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? |
#22
|
|||
|
|||
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? |
#23
|
|||
|
|||
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()-C22922,"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? |
#24
|
|||
|
|||
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()-C22922,"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? |
#25
|
|||
|
|||
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()-C22922,"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? |
#26
|
|||
|
|||
Rule of 75 Retirement Calculation
To build on Spiky's formula:
=IF(TODAY()-C22922,"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()-C22922,"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? |
#27
|
|||
|
|||
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()-C22922,"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()-C22922,"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? |
#28
|
|||
|
|||
Rule of 75 Retirement Calculation
Format the cell as a date (probably the best way) or:
=IF(TODAY()-C22922,"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()-C22922,"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()-C22922,"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? |
Thread Tools | |
Display Modes | |
|
|