IF Statement (Maybe complex) Need MAJOR Help

[HotNumbers2001]

IF Formula:


I have to generate a date that is between 5 different Dates: for Example

I have a starting date of 10/23/02

Date 1 = 3/26/02
Date 2 = 3/26/03
Date 3 = 3/26/04

I need a formula that will give me the answer for date 2: 3/26/03

If Starting date should change to 10/23/03

I need the answer to be 3/26/04

To make more complex the Starting date could change to anything:

Also if Starting date should change to 1/23/02 I need the answer to be Date 1 3/26/02

Can someone please help me. I have been going crazy trying to write an IF statement.

 

[Aladin Akyurek]

On 2002-10-23 14:08, HotNumbers2001 wrote:
IF Formula:


I have to generate a date that is between 5 different Dates: for Example

I have a starting date of 10/23/02

Date 1 = 3/26/02
Date 2 = 3/26/03
Date 3 = 3/26/04

I need a formula that will give me the answer for date 2: 3/26/03

If Starting date should change to 10/23/03

I need the answer to be 3/26/04

To make more complex the Starting date could change to anything:

Also if Starting date should change to 1/23/02 I need the answer to be Date 1 3/26/02

Can someone please help me. I have been going crazy trying to write an IF statement.

Wouldn't

=DATE(YEAR(A1)+1,MONTH(A1),26)

where A1 houses a start date, suffice?

 

[HotNumbers2001]

how would I go about using this formula.

if my starting date is in Cell (A1)

the other dates are in Cell (B1) = 3/26/02; (C1) = 3/26/03; (D1) = 3/26/04, etc.. I need the answer to be in H1

Thanks for the quick reply

 

[Aladin Akyurek]

On 2002-10-23 14:18, HotNumbers2001 wrote:
how would I go about using this formula.

if my starting date is in Cell (A1)

the other dates are in Cell (B1) = 3/26/02; (C1) = 3/26/03; (D1) = 3/26/04, etc.. I need the answer to be in H1

Thanks for the quick reply

I'm getting confused here...

You could put the formula in H1. The formula picks up the start date from A1. I interpreted "the other dates" as results that must obtain with different start-date values. Maybe I'm missing something.

If lasw10's interpretation is correct, you could use in H1...

=INDEX(B1:D1,MATCH(A1,B1:D1)+1)

Adjust the range B1:D1 to suit. But the earlier formula will compute the same thing directly.
This message was edited by Aladin Akyurek on 2002-10-23 14:34

 

[DonkeyOte]

On 2002-10-23 14:18, HotNumbers2001 wrote:
how would I go about using this formula.

if my starting date is in Cell (A1)

the other dates are in Cell (B1) = 3/26/02; (C1) = 3/26/03; (D1) = 3/26/04, etc.. I need the answer to be in H1

Thanks for the quick reply

H1 = IF(A1>D1,D1,if(A1>C1,C1,IF(A1>B1,B1,A1)))

Because of the order of the ifs you shouldn't need IF ANDs

 

[HotNumbers2001]

Thanks everyone.... I still can't get it to work. I will try to explain it better.

I need to find out when someones contract dates end given a specific date in time:
Some people have 5 different contract end dates, wheres some have three. In five different columns I have inputed there contract end dates.

Lates take someone who has five different End dates.

Date one End date is 3/26/02
Date two End date is 3/26/03
Date three End date is 3/26/04
Date Four End date is 3/27/05
Date five End date is 3/27/06

for Example if I wanted to know which date (1,2,3,4,or 5) is after a starting date:

So. IF I wanted to know who's contract is up after 10/23/02 I should get the answer Date TWO. Since date to is between 10/23/02 and 3/26/02. If I should change the start date from 10/23/02 to 10/23/03 then I should have date three, etc...

I hope this helps....

 

[Aladin Akyurek]

On 2002-10-23 14:39, HotNumbers2001 wrote:
Thanks everyone.... I still can't get it to work. I will try to explain it better.

I need to find out when someones contract dates end given a specific date in time:
Some people have 5 different contract end dates, wheres some have three. In five different columns I have inputed there contract end dates.

Lates take someone who has five different End dates.

Date one End date is 3/26/02
Date two End date is 3/26/03
Date three End date is 3/26/04
Date Four End date is 3/27/05
Date five End date is 3/27/06

for Example if I wanted to know which date (1,2,3,4,or 5) is after a starting date:

So. IF I wanted to know who's contract is up after 10/23/02 I should get the answer Date TWO. Since date to is between 10/23/02 and 3/26/02. If I should change the start date from 10/23/02 to 10/23/03 then I should have date three, etc...

I hope this helps....

See my edited reply with an INDEX formula added.
This message was edited by Aladin Akyurek on 2002-10-23 14:43

 

[HotNumbers2001]

ALADIN AKYUREK, You are a GOD. the INDEX formula you have me Worked. Thanks.....

 

[HotNumbers2001]

ALADIN, Your simple formula has safed me hours of work. I just have to impliment it to over 1,000 employees now. And I wish it was a simple copy and paste.

Thanks once again...