If Formula ?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

t long

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

Jan - Jul and;

Aug - Dec


again, sorry about that!
 
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
 

Attachments

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
 
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top