Combo of ISBLANK and MIN formula

[johns99]

Hello,

I used the below formula to pull in the earliest date found in different cells:

=IF(AND(ISBLANK(BC112),ISBLANK(AY112),ISBLANK(AU112),ISBLANK(AQ112),ISBLANK(AM112),ISBLANK(AK112),ISBLANK(AG112),ISBLANK(AC112),ISBLANK(Y112),ISBLANK(U112),ISBLANK(S112),ISBLANK(O112),ISBLANK(M112),ISBLANK(I112)),"",MIN(BC112,AY112,AU112,AQ112,AM112,AK112,AG112,AC112,Y112,U112,S112,O112,M112,I112))

To clarify, this formula works, but I'm wondering if there was an easier way to do this versus doing several ISBLANK formulas.

Thanks in advance,

John

 

[AlanY]

what is the purpose of the isblank()s?

 

[johns99]

So I'm trying to capture the earliest date from the cells referenced in the formula, some of these cells are blank. If I don't use the ISBLANK forumla the earliest date that pulls in is 1/1/1900. So I want to ignore the cells that are blank and only pull the ones that have dates.

 

[AlanY]

the min() function should ignore blank cells and 1/1/1900 equal = 1, not zero.

so, you must have a value of 1 within the range you're applying the min() function.
care to post the data that give the 1/1/1900 ?

 

[johns99]

Sure, here you go:

[TABLE="width: 460"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]Payment Date[/TD]
[TD]Payment Date[/TD]
[TD]Payment Date[/TD]
[TD]Payment Date[/TD]
[TD]Payment Date[/TD]
[TD]Payment Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/1/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5/1/2017[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1/0/1900[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1/0/1900[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1/0/1900[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1/0/1900[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1/0/1900[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1/0/1900[/TD]
[/TR]
[TR]
[TD]5/1/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5/1/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

this is what I got

Book1
	A	B	C	D	E	F	G
1	Payment Date	Payment Date	Payment Date	Payment Date	Payment Date	Payment Date
2	05/01/2017						05/01/2017
3							1/0/1900
4							1/0/1900
5							1/0/1900
6							1/0/1900
7							1/0/1900
8							1/0/1900
9	04/01/2017						05/01/2017
10
11	04/01/2017
Sheet1

Cell Formulas
Range	Formula
A11	=MIN(A2:G9)