1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Help in date formula

Discussion in 'Business Applications' started by ayush, Dec 29, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. ayush

    ayush Thread Starter

    Joined:
    Mar 27, 2005
    Messages:
    86
    A person retires at the age of 58 years. If his date of birth is on any day between 2nd and month end.
    Ex: if DOB is 15-10-2000 date of retirement will be 31-10-2058

    This formula works OK for above
    =DATE(YEAR(A1)+58,MONTH(A1)+1,DAY(0))

    But if the date of birth is on 1st day of the month his retirement will be last day of previous month.
    Ex:
    01 Oct 2010 his retirement date will be 30 Nov 2058
    01 Jan 1996 his retirement date will be 31 Dec 2033

    I want both of them together using if or any other mode of formula

    Pl. help me with suitable formula

    [​IMG]
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,203
    First Name:
    Wayne
    this may work - not tested fully
    I used in row 2


    =IF(DATE(YEAR(A2),MONTH(A2),DAY(A2))=DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A2)+58,MONTH(A2),DAY(0)),DATE(YEAR(A2)+58,MONTH(A2)+1,DAY(0)))
     
  3. ayush

    ayush Thread Starter

    Joined:
    Mar 27, 2005
    Messages:
    86
    Dear Sir,

    It worked. Thank You and a Happy New Year to You and Tech Support Guy Team

    [​IMG]
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,203
    First Name:
    Wayne
    excellent - thanks for the card :) (y)
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Less is more. :)

    =DATE(YEAR(A1)+58,IF(DAY(A1)=1,MONTH(A1),MONTH(A1)+1),DAY(0))
     
  6. ayush

    ayush Thread Starter

    Joined:
    Mar 27, 2005
    Messages:
    86
    Thank You verymuch sir.

    [​IMG]
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,203
    First Name:
    Wayne
    much neater

    Happy New Year
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    TBH I just wanted my own card. :D
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,203
    First Name:
    Wayne
    and very nice they are too
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1033530

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice