Results 1 to 5 of 5

Calculate retirement age which is date of birth plus sixty years

This is a discussion on Calculate retirement age which is date of birth plus sixty years within the Excel Questions forums, part of the Question Forums category; I need to calculate retirement age. 1. A per son retires on the last day of the month he completes ...

  1. #1
    Board Regular
    Join Date
    Apr 2003
    Location
    India
    Posts
    167

    Default Calculate retirement age which is date of birth plus sixty years

    I need to calculate retirement age.
    1. A per son retires on the last day of the month he completes 60 years. Say if born on 02 july 1967 will retire on 31 July 2027. Person born on 14 Feb 1952 will retire on 29 Feb 2012.

    2. Only other condition is that if the person is born on 1st of any month he retires on last day of the previous month. Say if born on 01 july 1967 will retire on 30 June 2027. Person born on 01 Feb 1952 will retire on 31 Jan 2012.

    I tried the following formula but stuck at how to get 31, 30, 28, 29 in respective months. If Date of Birth is in cell B1 then formula in other cell:
    =IF(DAY(B1)=1,(DATE(YEAR(B1)+60,MONTH(B1)-1,DAY(30))),(DATE(YEAR(B1)+60,MONTH(B1),DAY(30))))


    Can you help me in making a formula for this.
    =OR (B2, NOT (B2) )
    You never finish a spreadsheet. You just stop working on it.

    http://uttamtravelogue.blogspot.in/p/blog-index.html

  2. #2
    Board Regular
    Join Date
    Mar 2008
    Posts
    2,638

    Default Re: Calculate retirement age which is date of birth plus sixty years

    Try using:

    =IF(DAY(A1)=1,DATE(YEAR(A1)+60,MONTH(A1),0),DATE(YEAR(A1)+60,MONTH(A1)+1,0))
    Where to upload Excel files so threads don't take 20+ posts to get the correct answer from guessing:

    MediaFire: http://www.mediafire.com/
    FileFactory: http://www.filefactory.com/

    Also, in your thread or in your signature TELL US WHAT VERSION OF EXCEL YOU'RE USING!!!

    I'm using Excel 2007. My solutions will be appropriate for that version unless you tell us otherwise.

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Location
    India
    Posts
    167

    Default Re: Calculate retirement age which is date of birth plus sixty years

    Hi mvptomlinson,
    Thanks I will try it.
    Last edited by uttamsaxena; Feb 10th, 2012 at 02:45 AM.
    =OR (B2, NOT (B2) )
    You never finish a spreadsheet. You just stop working on it.

    http://uttamtravelogue.blogspot.in/p/blog-index.html

  4. #4
    Board Regular
    Join Date
    Mar 2008
    Posts
    2,638

    Default Re: Calculate retirement age which is date of birth plus sixty years

    Which is it? In your first post you said only those born on the FIRST of the month retire on the last day of the previous month, while all others retire on the last day of the month they were born in.

    Now you're saying those born on the FIRST and SECOND retire on the last day of the previous month, and those born on the 3-31 retire at the end of that month?
    Where to upload Excel files so threads don't take 20+ posts to get the correct answer from guessing:

    MediaFire: http://www.mediafire.com/
    FileFactory: http://www.filefactory.com/

    Also, in your thread or in your signature TELL US WHAT VERSION OF EXCEL YOU'RE USING!!!

    I'm using Excel 2007. My solutions will be appropriate for that version unless you tell us otherwise.

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    India
    Posts
    167

    Default Re: Calculate retirement age which is date of birth plus sixty years

    Hi mvptomlinson,
    I am sorry for some wrong calculation, but your formula works perfectly. Thanks a lot for the help.
    =OR (B2, NOT (B2) )
    You never finish a spreadsheet. You just stop working on it.

    http://uttamtravelogue.blogspot.in/p/blog-index.html

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com