Find minimum date withing range by finding valid date

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

Find minimum date withing range by finding valid date

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

User avatar
HansV
Administrator
Posts: 77587
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find minimum date withing range by finding valid date

Post by HansV »

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: 734
Joined: 24 Aug 2017, 07:43

Re: Find minimum date withing range by finding valid date

Post by BOOBALAN.V »

Thank you sir. As usual you made the solution in very simple manner what seemed complex one at least in my opinion :clapping:
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.

User avatar
HansV
Administrator
Posts: 77587
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find minimum date withing range by finding valid date

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

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

Re: Find minimum date withing range by finding valid date

Post by 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.

User avatar
p45cal
2StarLounger
Posts: 133
Joined: 11 Jun 2012, 20:37

Re: Find minimum date withing range by finding valid date

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

Re: Find minimum date withing range by finding valid date

Post by snb »

Or in this case: ctrl-shft-Enter

Code: Select all

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

User avatar
rory
5StarLounger
Posts: 797
Joined: 24 Jan 2010, 15:56

Re: Find minimum date withing range by finding valid date

Post by rory »

Or if you have MINIFS:

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

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

Re: Find minimum date withing range by finding valid date

Post by BOOBALAN.V »

Thank you all for your time and useful solutions.