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.
Find minimum date withing range by finding valid date
-
- 5StarLounger
- Posts: 742
- Joined: 24 Aug 2017, 07:43
Find minimum date withing range by finding valid date
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 79662
- 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))
=MIN(IF(MOD(COLUMN(A2:I2),3)=2,A2:I2))
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 742
- 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.
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.
-
- Administrator
- Posts: 79662
- 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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 742
- 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.
-
- 2StarLounger
- Posts: 171
- 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))
-
- 5StarLounger
- Posts: 619
- 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))
-
- 5StarLounger
- Posts: 826
- 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*")
=MINIFS(B2:I2,$B$1:$I$1,"*date*")
Regards,
Rory
Rory
-
- 5StarLounger
- Posts: 742
- 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.