# If Formula ?

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

Not open for further replies.

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

2. ### Rollin_Again

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

Rollin

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

Jan - Jul and;

Aug - Dec

4. ### 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:

• ###### ContractDates.zip
File size:
1.6 KB
Views:
8

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

Rollin

As Seen On