Page 1 of 3 123 LastLast
Results 1 to 10 of 23

how to find date of retirement

This is a discussion on how to find date of retirement within the Excel Questions forums, part of the Question Forums category; Dear Friends, kindly help me to solve the following problem in excel i need a excel programme to determine the ...

  1. #1
    Board Regular
    Join Date
    Aug 2008
    Posts
    83

    Default how to find date of retirement

    Dear Friends,

    kindly help me to solve the following problem in excel

    i need a excel programme to determine the date of retirement of employees as per their date of birth.

    if any employees date of is : 01-07-1990, he should be retired from service on 30-06-2048, taking 58 years of age as retirement year.

    2) date of birth: 15-07-1990 date of retirement is:31-07-2048

    how to calculate these,

    my input in a1 is date of birth, b1 is age of retirement, the result should be in c1 adding date of birth + age of retirement.

    thank you

  2. #2
    Board Regular Dannyh1's Avatar
    Join Date
    Nov 2009
    Location
    Liverpool UK
    Posts
    1,126

    Default Re: how to find date of retirement

    Hi

    What are the rules?

    i.e I would have expected scenario 2 date to be 30/06/2048

  3. #3
    Board Regular
    Join Date
    Jul 2010
    Location
    Doha, Qatar (Arabian Gulf)
    Posts
    133

    Default Re: how to find date of retirement

    Following is the formula if Date of Birth is in A1 cell:

    =IF(DAY(A1)<15,EOMONTH(DATE(YEAR(A1)+58,IF(MONTH(A1)=1,12,MONTH(A1)-1),1),0),EOMONTH(DATE(IF(MONTH(A1)=1,YEAR(A1)+58,YEAR(A1)+59),IF(MONTH(A1)=1,12,MONTH(A1)+1),1),0))

    Regards,

  4. #4
    Board Regular
    Join Date
    Aug 2008
    Posts
    83

    Default Re: how to find date of retirement

    Thanks for suggestions.

    but date only after 15 is taking for change of month or year

    i mean if i enter date as 12/14/1950 mm/dd/yy the date of retirement is coming as 11/20/2008 and 12/15/1950 is as 1/31/20010, why this type of errors are occuring.

    date format should be dd/mm/yy

    date of retirement : add 58 years to date of birth

    date of retiremnet of persons who born on 1 of the month he should retired month before to this date of birth i.e. 1/06/1050 he should retired on 31/05/2008

    kinly help

  5. #5
    Board Regular
    Join Date
    Jul 2010
    Location
    Doha, Qatar (Arabian Gulf)
    Posts
    133

    Default Re: how to find date of retirement

    If you want:
    Date of Birth is 01-07-1990 then Date of retirement is 30-06-2048.
    Date of Birth is 15-07-1990 then Date of retirement is 31-07-2048.
    Date of Birth is 02-07-1990 then Date of retirement is 31-07-2048.

    Please apply the following formula:
    If date of birth is in A1 cell:

    =IF(DAY(A1)=1,EOMONTH(DATE(YEAR(A1)+58,IF(MONTH(A1)=1,12,MONTH(A1)-1),1),0),EOMONTH(DATE(YEAR(A1)+58,MONTH(A1),1),0))

    If date of birth in A1 cell and Age of retirement in b1 cell, please consider "B1" instead of 58 or the formula in C1 cell should be as under:

    =IF(DAY(A1)=1,EOMONTH(DATE(YEAR(A1)+B1,IF(MONTH(A1)=1,12,MONTH(A1)-1),1),0),EOMONTH(DATE(YEAR(A1)+B1,MONTH(A1),1),0))

    I hope this will solve your problem.

  6. #6
    Board Regular
    Join Date
    Aug 2008
    Posts
    83

    Default Re: how to find date of retirement

    The formula is not working, some sytax error is there

  7. #7
    Board Regular
    Join Date
    Dec 2008
    Posts
    5,698

    Default Re: how to find date of retirement

    Anything wrong with

    =EOMONTH(A1,(12*B1)-1)

  8. #8
    Board Regular
    Join Date
    Jul 2008
    Location
    Surrey, UK
    Posts
    1,477

    Default Re: how to find date of retirement

    what version of Excel are you using ? If you are using Excel 2003 or earlier, you will need the analysis tool pak to be installed to use EOMONTH. This can be found under Tools, Options (I seem to remember). Thanks

    Regards

    Kaps
    Read my Excel blog on

    http://simplyspreadsheets.wordpress.com/

    For more ways I can help you with Excel :-

    www.simplyspreadsheets.co.uk

  9. #9
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,971

    Default Re: how to find date of retirement

    Quote Originally Posted by jasonb75 View Post
    Anything wrong with

    =EOMONTH(A1,(12*B1)-1)
    If the requirement is for 1st of the month to go to last day of previous month but any other date to the end of that month then that still wouldn't work, although this would

    =EOMONTH(A1-1,12*B1)

    If you get a syntax error try changing , to ;

    without EOMONTH you could use

    =DATE(YEAR(A1-1)+B1,MONTH(A1-1)+1,0)

  10. #10
    Board Regular
    Join Date
    Dec 2008
    Posts
    5,698

    Default Re: how to find date of retirement

    Barry, I think we both misread the requirement, reading through the thread again carefully I'm suggesting

    =EOMONTH(A1,(12*58)-(DAY(A1)<15))

    Any thoughts for improvement?

Page 1 of 3 123 LastLast

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