MIN IF function searching for date, ignoring blank cells

[Gijs]

Hope someone can help me,

I'm trying to identify the earliest date in the month (of January) from a range ($J$13:$J$320).

• =MIN(IF(1=MONTH($J$13:$J$320),$J$13:$J$320,"")) (Ctr+Shift+Enter).

It works like a charm, as long as the range doesn't contain a blank cell, which it does.

• =MINA(IF(1=MONTH($J$13:$J$320),$J$13:$J$320,"")) yields the same false result: Jan 00 '00
• =MINIFS formula is not an option; its not on my Excel 2011

Can anyone help me with my MIN IF a formula searching for date, ignoring blank cells?

Thanks in advance,
Gijs

 

[JackDanIce]

Adjust range to suit and try:

=MIN(IF(MONTH($A$2:$A$10)=1,IF(ISNUMBER($A$2:$A$10),$A$2:$A$10)))

with CTRL+SHIFT+ENTER

 

[Gijs]

Awesome! Great Jack, thanks. And have a wonderful rest of the weekend!

 

[Peter_SSs]

Another option (for Excel 2010 or later) that does not require the Ctrl+Shift+Enter

=AGGREGATE(15,6,$J$13:$J$320/((MONTH($J$13:$J$320)=1)*($J$13:$J$320<>"")),1)

 

[JackDanIce]

You're welcome Gjs, though I'd use Peter_SSs example, just to avoid array formulae!

 

[Gijs]

Thank you Peter; Hugely appreciated!

Any ideas as to how I can avoid a Jan 00 '00 error when the range ($J$13:$J$320) doesn't have any date in the month
=MIN(IF(MONTH($J$13:$J$210)=10,IF(ISNUMBER($J$13:$J$210),$J$13:$J$210)))

Thanks again Peter. And wishing you already a good Sunday morning!

 

[JackDanIce]

Quick answer, wrap the function in

=ISERROR(function,””)

 

[Gijs]

Thanks for the fast response Jack.

Regrettably the formula returns a FALSE
=ISERROR(MIN(IF(MONTH($J$13:$J$210)=10,IF(ISNUMBER($J$13:$J$210),$J$13:$J$210,"")))) (Ctr+Shift+Enter)

Or have I misunderstood your instruction?

 

[JackDanIce]

Nope, I forgot it was an array function, @Peter_Ss any suggestion?

 

[Peter_SSs]

Try
=IFERROR(AGGREGATE(15,6,$J$13:$J$320/((MONTH($J$13:$J$320)=1)*($J$13:$J$320<>"")),1),"")

If you want (or need) to continue with the C+S+E type formula
{=IF(COUNT($J$13:$J$320),MIN(IF(MONTH($J$13:$J$320)=1,IF(ISNUMBER($J$13:$J$320),$J$13:$J$320))),"")}