+ Reply to Thread
Results 1 to 8 of 8

How to find the date of retirement if date of birth is 1-April-1980

  1. #1
    Registered User
    Join Date
    07-01-2008
    Location
    Pakistan
    Posts
    5

    How to find the date of retirement if date of birth is 1-April-1980

    Hi every body,

    I want to find date of retirement by adding 60 years with date of birth and subtracting 1 day.
    I used the following formula which I learnt from a forum.

    Cell A1=date of birth.

    Cell B1= Date of retirement

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

    But the problem with this formula is that if the date of birth is 1-April-1980 then it gives 1-May-2040 which is wrong. It should be 31-March-2040.

    Secondly if the retirement date is Sunday or Saturday it should give date for Friday as retirement date.

    I hope I will get the solution from experts on forum.

    Regards
    Mmashah

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: How to find the date of retirement if date of birth is 1-April-1980

    Change the following...

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

  3. #3
    Registered User
    Join Date
    12-09-2009
    Location
    Denver, NC
    MS-Off Ver
    2007
    Posts
    35

    Re: How to find the date of retirement if date of birth is 1-April-1980

    =DATE(YEAR(A1)+60,MONTH(A1),DAY(A1))-1

    or

    =15+(365*60)+A1-1

  4. #4
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: How to find the date of retirement if date of birth is 1-April-1980

    For your second question... it's messy, but it works... I'm sure there are more elegant ways to solve it...

    =IF(WEEKDAY(DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-1))=1,DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-3),IF(WEEKDAY(DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-1))=7,DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-2),DATE(YEAR(P3)+Q3,MONTH(P3),DAY(P3)-1)))

    Note: WEEKDAY gives you a value of 1 for Sunday, 2 for Monday, ... , 7 for Saturday...

  5. #5
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    676

    Re: How to find the date of retirement if date of birth is 1-April-1980

    ... and what about national holidays ?

  6. #6
    Registered User
    Join Date
    07-01-2008
    Location
    Pakistan
    Posts
    5

    Re: How to find the date of retirement if date of birth is 1-April-1980

    Excellent it worked.

  7. #7
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: How to find the date of retirement if date of birth is 1-April-1980

    do you have Excel 2003, or higher version, and Analysis Toolpak installed?

    if so, with 01-APR-1980 in A1, this formula in B1 will give you 30-MAR-2040 (31st is Saturday):

    Please Login or Register  to view this content.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  8. #8
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: How to find the date of retirement if date of birth is 1-April-1980

    Beautiful solution, Ice...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1