Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Formula for retirement date

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need a formula for the following:

    Cell E45 = 8/9/2002 (retire date)
    Cell E46 = 6/1/1943 (birth date)

    In cell E47, I will be subtracting the two dates to get the age in years and months.

    In cell E48, I will be subtracting the two dates to get the age in months.

    I need it to compute as follows: if cell E45 is <=28 (28th day of month), then I need it to compute effective the following month -- in this case, 9/1/2002. If cell E45 is >=28, then compute on that date.

    Thanks in advance for your help.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    G'day,

    Cell E45 = 8/9/2002 (retire date)
    Cell E46 = 6/1/1943 (birth date)

    In cell E47, I will be subtracting the two dates to get the age in years and months.

    In cell E48, I will be subtracting the two dates to get the age in months.

    I need it to compute as follows: if cell E45 is <=28 (28th day of month), then I need it to compute effective the following month -- in this case, 9/1/2002. If cell E45 is >=28, then compute on that date.
    I believe you want to use the DATEDIF function - but wrapped with an IF-statement to cover the months ending in days after the 28th.

    =IF(DAY(E45)<=28,DATEDIF(E46,E45,"m"),DATEDIF(E46,E45,"m")+1)

    or the same formula re-written:

    =DATEDIF(E46,E45,"m")+(IF(DAY(E45)>28,1,0)

    for the number of months difference

    Hope that helps,
    Adam

Some videos you may like

User Tag List

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
  •