IF Statement (Maybe complex) Need MAJOR Help

HotNumbers2001

Board Regular
Joined
Apr 21, 2002
Messages
139
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Board Regular
Joined
Apr 21, 2002
Messages
139
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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

Board Regular
Joined
Apr 21, 2002
Messages
139
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

Board Regular
Joined
Apr 21, 2002
Messages
139
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top