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.

If Formula ?

Discussion in 'Business Applications' started by t long, Feb 13, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    Hi again

    I need a formula that will calculate a military members new contract end date based on their date of birth and their current contract end date.

    In cell D3 I have the members "current contract end date"
    In cell D4 I have the members "Date of Birth"

    And in Cell D6 I need the formula to calculate the f"new contract end date based on the following :

    If the "current contract end date" ends in January - June the "new contract end date" will be DOB + "Jul"y + 3 yrs (from current contract end date)

    Example : members Bday is 30 March and current contract ends 1 Apr 94 the new contract end date will be 30 Jul 1997

    If the "current contract end date" ends August - September the "new contract end date" will be DOB + "July" + 4 yrs (from current contract end date)

    Example : members Bday is 15 Mar and "current contract ends 20 Sep 94 the new contract end date will be 15 July 98

    If the "current contract end date" ends in Jul and DOB is before the "current contract end date" - DOB + "July" + 4 yrs

    Example : Members Bday is 5 July and current contract end date is 15 Jul 91 the new contract end date will be 5 July 95

    and last but not least

    If the "current contract end date" ends in Jul and DOB is after the "current contract end date" - DOB + "July" + 3 yrs

    Example : members Bday is 30 Mar and "current contract end date" is 14 Jul 94 the new contract end date will be 30 jul 97

    Please help - thanks
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    What about birthdays in October, November, and December?

    Rollin
     
  3. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    Sorry that's a typo

    Jan - Jul and;

    Aug - Dec


    again, sorry about that!
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Format Cell D6 as a date and put the following formula in there. By the way, I still have a headache from working on this ;)

    =IF(MONTH(D3)>=1,IF(MONTH(D3)<=6,DATE(YEAR(D3)+3,7,DAY(D4)),IF(MONTH(D3)=7,IF(DATE(2005,MONTH(D4),DAY(D4))>DATE(2005,MONTH(D3),DAY(D3)),DATE(YEAR(D3)+3,7,DAY(D4)),DATE(YEAR(D3)+4,7,DAY(D4))),DATE(YEAR(D3)+4,7,DAY(D4))))," ")



    P.S. - SAMPLE WORKBOOK ATTACHED


    Rollin
     

    Attached Files:

  5. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    The formula worked great !

    put in a couple of scenerios and everything worked perfect!

    Thank you Thank you Thank you
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I'm glad I could help you. If you get a chance, mark this problem as solved using the thread tools.

    Rollin
     
  7. 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/330072

  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