A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Rule of 75 Retirement Calculation



 
 
Thread Tools Display Modes
  #1  
Old October 17th 08, 04:16 PM posted to microsoft.public.excel.worksheet.functions
BAD
external usenet poster
 
Posts: 13
Default 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?
Ads
  #2  
Old October 17th 08, 04:42 PM posted to microsoft.public.excel.worksheet.functions
ShaneDevenshire
external usenet poster
 
Posts: 2,344
Default 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  
Old October 17th 08, 05:03 PM posted to microsoft.public.excel.worksheet.functions
~L
external usenet poster
 
Posts: 177
Default 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?

  #4  
Old October 17th 08, 05:06 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,504
Default 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?

  #5  
Old October 17th 08, 05:31 PM posted to microsoft.public.excel.worksheet.functions
Glenn
external usenet poster
 
Posts: 1,240
Default 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
  #6  
Old October 17th 08, 05:33 PM posted to microsoft.public.excel.worksheet.functions
Glenn
external usenet poster
 
Posts: 1,240
Default 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.
  #7  
Old October 17th 08, 05:59 PM posted to microsoft.public.excel.worksheet.functions
BAD
external usenet poster
 
Posts: 13
Default 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?

  #8  
Old October 17th 08, 06:00 PM posted to microsoft.public.excel.worksheet.functions
BAD
external usenet poster
 
Posts: 13
Default 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?

  #9  
Old October 17th 08, 06:01 PM posted to microsoft.public.excel.worksheet.functions
BAD
external usenet poster
 
Posts: 13
Default 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?

  #10  
Old October 17th 08, 06:15 PM posted to microsoft.public.excel.worksheet.functions
~L
external usenet poster
 
Posts: 177
Default 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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to copy from Fidelity Retirement Income Planner? [email protected] Excel Discussion (Misc queries) 0 August 25th 07 11:36 AM
How to copy from Fidelity Retirement Income Planner? [email protected] Excel Discussion (Misc queries) 0 August 25th 07 11:36 AM
How do i set up a sheet to count the years to retirement? dee29 Excel Worksheet Functions 1 April 6th 06 11:55 AM
I need to know formula for matching retirement percents pebbles2005 Excel Worksheet Functions 1 March 31st 05 09:02 PM
NPER - Retirement Example mschumacker Excel Worksheet Functions 2 March 13th 05 08:21 PM


All times are GMT +1. The time now is 06:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.