Find minimum date withing range by finding valid date

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Find minimum date withing range by finding valid date

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.
You do not have the required permissions to view the files attached to this post.

HansV
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find minimum date withing range by finding valid date

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

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

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: Find minimum date withing range by finding valid date

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
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find minimum date withing range by finding valid date

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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: Find minimum date withing range by finding valid date

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
2StarLounger
Posts: 139
Joined: 11 Jun 2012, 20:37

Re: Find minimum date withing range by finding valid date

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
4StarLounger
Posts: 541
Joined: 14 Nov 2012, 16:06

Re: Find minimum date withing range by finding valid date

Or in this case: ctrl-shft-Enter

Code: Select all

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

rory
5StarLounger
Posts: 813
Joined: 24 Jan 2010, 15:56

Re: Find minimum date withing range by finding valid date

Or if you have MINIFS:

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

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: Find minimum date withing range by finding valid date

Thank you all for your time and useful solutions.