Excel Formula for Choosing Date 2

[beadedbytes]

I've been trying to figure out if there's an Excel formula that would accomplish the following:

ORIG_DT	        DATE_1	        DATE_2	        DATE_3	        DATE_4	        RESULT
11/17/2011	4/13/2010	7/13/2010	10/21/2010	12/27/2011	12/27/2011
5/29/2013	7/3/2013	8/10/2013	9/16/2013	11/29/2013	7/3/2013
8/16/2012	12/12/2009		        1/16/2013	7/11/2013	1/16/2013
9/18/2013	7/13/2011	10/12/2013	11/5/2013	12/6/2013	10/12/2013
5/16/2012	7/14/2011		        6/15/2012	8/11/2013	6/15/2012

For every row, find the 1st date in the DATE_1 through DATE_4 range that falls *after* the ORIG-DT.
Return that date in the RESULT field.

NOTE: Blank cells should be 'ignored' in the analysis.

Thanks in advance for feedback!

 

[SkipVought]

hi,

This solution is very specific to your example and may not be correct if your column data extends beyond Date4 or if you have empty cell in other columns than in your example.
[tt]
F2: =IF(IFERROR(INDEX(B2:E2,1,MATCH(A2,B2:E2,1)+1),B2)=0,IFERROR(INDEX(C2:E2,1,MATCH(A2,C2:E2,1)+1),D2),IFERROR(INDEX(B2:E2,1,MATCH(A2,B2:E2,1)+1),B2))
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

Alternative solution
F2: {=MIN(IF(B2:E2>=$A$2;B2:E2;DATE(9999;12;31)))}

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

@vgulielmus, does not work for the rows containing empty cells.

This might work better
[tt]
F2: =IF(INDEX(B2:E2,1,IFERROR(MATCH(A2,B2:E2,1)+1,1))=0,INDEX(B2:E2,1,IFERROR(MATCH(A2,B2:E2,1)+2,1)),INDEX(B2:E2,1,IFERROR(MATCH(A2,B2:E2,1)+1,1)))
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

It's a tested formula. I tested it again and gives the expected result.
Maybe we have different versions of Excel?

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

so does your formula in your sheet result in 1/16/2013 on row where column A value is 8/16/2012?

Your formula in my sheet results in 7/11/2013, rather than 1/16/2013.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

Yes, all five rows gives the expected result

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

Hmmmm.

The expected results are
[tt]
12/27/2011
7/3/2013
1/16/2013
10/12/2013
6/15/2012
[/tt]

The results using your formula {=MIN(IF(B2:E2>=$A$2,B2:E2,DATE(9999,12,31)))} is
[tt]
12/27/2011
7/3/2013
[highlight #FCE94F]7/11/2013[/highlight]
10/12/2013
[highlight #FCE94F]8/11/2013[/highlight]
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

Maybe you are referring at my typo
{=MIN(IF(B2:E2>=$A$2;B2:E2;DATE(9999;12;31)))}
instead of
{=MIN(IF(B2:E2>=$A2;B2:E2;DATE(9999;12;31)))}

I apologize for that.


My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

Hm... thanks
I have tested my sheet not the formula typed here.
Once again, I apologize.

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

Yes and I also used this {=MIN(IF(B2:E2>=$A2,B2:E2,DATE(9999,12,31)))}
...with the SAME RESULTS as I posted. NOTHING CHANGED

The only other difference between your literal version and mine is the ; vs , delimiter in the formula which is European formula delimiter vs USA formula delimiter.

BOTTOM LINE: I cannot produce the expected results using your formula with $A2 or A2 in an array expression!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

Now that's odd
I tested on two different computers and the result is good on both of them.
{=IF(B2:E2>=$A2;B2:E2;DATE(9999;12;31))}

produce:
date(9999,12,31) date(9999,12,31) date(2013,01,16) date(2013,07,11)


My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

Now I am TOTALY confused by ...

produce:
date(9999,12,31) date(9999,12,31) date(2013,01,16) date(2013,07,11)

I have no idea what that is supposed to mean!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

It means
12/31/9999 12/31/9999 01/16/2013 07/11/2013
but on my PC is
31.12.9999 and so on
so I use the DATE() function.

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

I know what the function produces, but what relevance does that specific post have to the question at hand?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

It's a hint that the formula used by me is correct.
The empty cell is evaluated as 12/31/9999 and the MIN() of the for dates is 01/16/2013, not 07/11/2013

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

Okay, now I understand what you meant.

And I can see that I had a data conversion issue when I pasted the data example into my sheet.

Your formula works PERFECTLY and I apologize for objecting so strenuously.

By the way, I discovered my error when I decided to evaluate the IF() function within your expression by 1) eding the formula, 2) selecting the entire IF() function and 3) hitting the F9 key. I had embedded SPACES, and when I delected them, your formula yielded the expected result.

Very sorry for the misunderstanding on my part.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

BTW, vgulielmus, your expression is much more elegant in its simplicity than mine, so I salute you.

You have further impressed me as has your late countryman, Richard Wurmbrand, but in a much different way.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

It was a pleasant conversation.
I was honored by your interest.

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

I read some of his books and I was very impressed.


My respects,
Vilhelm-Ion Praisach
Resita, Romania