Help with arrays and if statements

[mkramer1]

I have three columns of dates. Each row corresponds to one item. I want to find the closest future date to today. So I used this
=MIN(IF(G23:I23>$E$1,G23:I23)). However, for two items, the dates are all less than TODAY(). For that I would like to find the furthest past date from TODAY() that is not 01-01-00. I would like a formula that I could drag down for all items, despite having future of past dates. I need this because my dates correspond to data from a web query, so the dates may change from past to future depending on the dates on the website. I tried using the formula below (I know I used MAX because I didn't know how to exclude 01-01-00), but it gives a #VALUE error sometimes, and sometimes it doesn't.

IF(MIN(IF(G27:I27>$E$1,G27:I27))=TRUE,MIN(IF(G27:I27>$E$1,G27:I27)),(DATE(YEAR(MAX(IF(G27:I27<$E$1,G27:I27)))+2,MONTH(MAX(IF(G27:I27<$E$1,G27:I27)))+6,DAY(MAX(IF(G27:I27<$E$1,G27:I27))))))

If anyone could help me figure this out, I would greatly appreciate it. I have tried reading about arrays but it hasn't gotten me anywhere .

The first picture shows the values when the first formula is used for all rows. The second picture shows the value error when only that row uses the latter formula.

[/URL][/IMG]

[/URL][/IMG]

 

[mkramer1]

[TABLE="width: 218"]
<tbody>[TR]
[TD="align: right"]19-06-18[/TD]
[TD="align: right"]19-06-16[/TD]
[TD="align: right"]19-06-20[/TD]
[/TR]
[TR]
[TD="align: right"]26-09-17[/TD]
[TD="align: right"]26-09-17[/TD]
[TD="align: right"]26-09-19[/TD]
[/TR]
[TR]
[TD="align: right"]01-03-21[/TD]
[TD="align: right"]01-12-19[/TD]
[TD="align: right"]31-03-18[/TD]
[/TR]
[TR]
[TD="align: right"]01-03-21[/TD]
[TD="align: right"]01-12-19[/TD]
[TD="align: right"]31-03-18[/TD]
[/TR]
[TR]
[TD="align: right"]01-03-16[/TD]
[TD="align: right"]01-03-16[/TD]
[TD="align: right"]00-01-00[/TD]
[/TR]
[TR]
[TD="align: right"]24-11-19[/TD]
[TD="align: right"]24-11-17[/TD]
[TD="align: right"]25-11-16[/TD]
[/TR]
[TR]
[TD="align: right"]21-10-19[/TD]
[TD="align: right"]21-10-17[/TD]
[TD="align: right"]22-10-16[/TD]
[/TR]
[TR]
[TD="align: right"]01-03-16[/TD]
[TD="align: right"]01-03-16[/TD]
[TD="align: right"]00-01-00[/TD]
[/TR]
[TR]
[TD="align: right"]14-05-18[/TD]
[TD="align: right"]14-05-18[/TD]
[TD="align: right"]14-05-20[/TD]
[/TR]
[TR]
[TD="align: right"]07-05-18[/TD]
[TD="align: right"]07-05-18[/TD]
[TD="align: right"]07-05-20[/TD]
[/TR]
</tbody>[/TABLE]
the actual data, sorry I forgot to include it in the post.

 

[wideboydixon]

I'm not quite sure what you mean by "furthest past date from TODAY()" so I've given two options:

Book1
	G	H	I	J	K
23	19-06-18	19-06-16	19-06-20	19-06-16	19-06-16
24	26-09-17	26-09-17	26-09-19	26-09-17	26-09-17
25	01-03-21	01-12-19	31-03-18	31-03-18	31-03-18
26	01-03-21	01-12-19	31-03-18	31-03-18	31-03-18
27	01-03-16	01-03-16	00-01-00	01-03-16	01-03-16
28	24-11-19	24-11-17	25-11-16	25-11-16	25-11-16
29	21-10-19	21-10-17	22-10-16	22-10-16	22-10-16
30	01-03-16	01-03-16	00-01-00	01-03-16	01-03-16
31	14-05-18	14-05-18	14-05-20	14-05-18	14-05-18
32	07-05-18	07-05-18	07-05-20	07-05-18	07-05-18
Sheet1

Cell Formulas
Range	Formula
J23	{=IF(MIN(IF(G23:I23>$E$1,G23:I23)) = 0, MIN(IF(G23:I23=0,99999,G23:I23)), MIN(IF(G23:I23>$E$1,G23:I23)))}
K23	{=IF(MIN(IF(G23:I23>$E$1,G23:I23)) = 0, MAX(G23:I23), MIN(IF(H23:J23>$E$1,G23:I23)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Column J should pick the earliest date that isn't 00-01-00. Column K should pick the nearest date to TODAY(). BTW, I have $E$1 = TODAY().

WBD

 

[mkramer1]

Thank you so much, this was extremely helpful!

By furthest past date, I meant that between the three dates, 01-01-15,01-01-16,01-01-00, it would return 01-01-15 since it is the furthest date, in the past, from TODAY() that isn't 01-01-00.

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

a​

[/TD]
[TD]

b​

[/TD]
[TD]

c​

[/TD]
[TD]

d​

[/TD]
[TD]

e​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

1/1/2015​

[/TD]
[TD]

1/1/2016​

[/TD]
[TD]

1/1/2000​

[/TD]
[TD][/TD]
[TD]

1/1/2015​

[/TD]
[/TR]
</tbody>[/TABLE]

e1=MIN(IF(A1:C1<TODAY(),IF(A1:C1<>C1,A1:C1))) control+shift+enter