Find minimum date withing range by finding valid date

[BOOBALAN.V]

Dear Community,

How to check valid dates in excel if range of cells may contain data like numbers or alpha numeric numbers and dates. I have attached the sample work book with formula what i have tried. It is not working as expected since excel is storing dates also in numbers. Kindly help me with this formula. Because in my organization VBA is restricted. Thank you for your support.

[HansV]

If the dates will be 3 columns apart, in columns B, E, H:

=MIN(IF(MOD(COLUMN(A2:I2),3)=2,A2:I2))

[BOOBALAN.V]

Thank you sir. As usual you made the solution in very simple manner what seemed complex one at least in my opinion
For learning purpose i am asking once again. I hope you don’t mind. If we want to check true date along with numbers, how to do sir? Is it possible to ignore numbers while checking dates? Kindly tell me. It will be useful for us in future, just in case.

Once again thank you for time and support.

[HansV]

The attached workbook demonstrates two ways using helper cells. One uses the CELL function, the other a custom VBA function IsADate.

Check valid dates only along with numbers.xlsm

[BOOBALAN.V]

Thank you so much sir. I have learned new things. It is very useful. This insights sparked me to work around too instead of only trying in straight forward. Once again thank you sir.

[p45cal]

You could use the fact that the string 'date' is in the headers to determine if there's meant to be a date in the column. This one also ignores blanks in any of the date columns. In cell K2:

Code: Select all

=MIN(IF(ISNUMBER(SEARCH("date",$A$1:$I$1))*(LEN(TRIM(A2:I2))>0),$A2:$I2))

[snb]

Or in this case: ctrl-shft-Enter

Code: Select all

=MIN(IF(LEN(A1:I1)=9;A2:I2))

[rory]

Or if you have MINIFS:

=MINIFS(B2:I2,$B$1:$I$1,"*date*")

[BOOBALAN.V]

Thank you all for your time and useful solutions.