Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 17th 08, 04:16 PM posted to microsoft.public.excel.worksheet.functions
BAD BAD is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
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?

  #2   Report Post  
Old October 17th 08, 04:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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   Report Post  
Old October 17th 08, 05:03 PM posted to microsoft.public.excel.worksheet.functions
~L ~L is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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   Report Post  
Old October 17th 08, 05:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
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   Report Post  
Old October 17th 08, 05:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
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   Report Post  
Old October 17th 08, 05:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
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   Report Post  
Old October 17th 08, 05:59 PM posted to microsoft.public.excel.worksheet.functions
BAD BAD is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
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   Report Post  
Old October 17th 08, 06:00 PM posted to microsoft.public.excel.worksheet.functions
BAD BAD is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
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   Report Post  
Old October 17th 08, 06:01 PM posted to microsoft.public.excel.worksheet.functions
BAD BAD is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
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   Report Post  
Old October 17th 08, 06:15 PM posted to microsoft.public.excel.worksheet.functions
~L ~L is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 05:47 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017