CODEWITHSUNDEEP
excelexcel-formulavba
RetroCoder
RetroCoder

Reputation: 2685

Multiple Excel dates are date filterable

Looking for a way to highlight or list all the column names that are date filterable (see images below). I can have several excel files and some of the dates are not date filterable and I need to be able to quickly see the ones that are and aren't date filterable as a time saver technique. I'd also like to create a macro button and add it to excel to be able to do this or add it if it already exists.

Tried to create an existing conditional formatting like this link. But not having success with Column Header on the type of filter being detected. There is a dates occurring. But its doesn't detect if its a filterable date and the options are too limited.

The solution I'd prefer to be a combination of excel formulas, but a vba script would also work. It could also be an advanced menu option but I don't think one exists. It may require the creation of a custom menu button.

> Date Filterable:

Date Filterable

> Not Date Filterable

Not Date Filterable

Thanks!

Upvotes: 0

Views: 62

Answers (1)

user4039065
user4039065

Reputation:

If a column of values that look like dates are 'date-filterable' then they are actually dates and not text-that-looks-like-a-date. Excel recognizes these on the worksheet as numbers so =ISNUMBER(A2) will be TRUE. =TYPE(A2 will also return 1 which designated it as a number. You can get more complicated than that but it really is all that you should require. Visually, true dates are right-aligned in a cell (by default) as numbers are. Text is left-aligned.

In VBA the IsNumeric(Range("A2")) equates to pretty much the same thing but you also have the option of IsDate(Range("A2")) which attempts to determine whether the value is not only numeric but also holds locale information that confirms it as a date-type value.

Upvotes: 1

Related Questions