Isabel has attended:
Excel Intermediate course
Filter by cell format possible?
Hi,
I am working with tables of over 10,000 rows that include columns with post- and pre-1900 dates. One of my tasks is to have one column reading the date in a dd month yyyy format and to have one column read the same in an integer or yyyy-mm-dd format.
Before I tackle slightly lengthier ways of doing this for pre-1900 dates, I wanted to see if I could get the post-1900 dates "out of the way". For this, is it possible to filter a table by the format of the cell (i.e. a cell with a post-1900 date that Excel has recognised as a date)? At the moment, I have been going through my sample manually to highlight post-1900 dates and sorted them by colour but I was wondering if there was a faster way of doing this? I am not sure which option under 'Create a New Rule' for Conditional Formatting to select or whether that is even the right path.
Many thanks for your advice in advance!
All the best,
Isabel
RE: Filter by cell format possible?
Hello Isabel,
Excel doesn't recognise dates before 1 January 1900, so none of Excel's date formatting, tools or functions will work on these.
The quickest way I could suggest to show dates from 1 Jan 1900 is to run a filter on dates on or after 1 Jan 1900.
Turn on filters, then run a Date filter on the dates column. Set the filter to "is after or equal to" 01/01/1900. You have to type the complete date as I have here.
This will filter out all "non-dates", i.e. before 1900.
I hope this helps.
Kind regards
Marius Barnard
STL
RE: Filter by cell format possible?
Hi Marius,
Thanks for your response. At the moment I don't have the option to select a 'Date' filter, only a 'Text' filter on the column. Could a reason for this be the slight inconsistency in the entries, as some dates are only known as yyyy since we don't know the specifics? Would there be a way around this or would it have to involve manual sorting?
Many thanks again.
Kind regards,
Isabel
RE: Filter by cell format possible?
Hi Isabel,
Because there is a mix of dates and non-dates in your data, Excel doesn't show a date filter. You could use the following IF statement to show which rows have proper dates:
Insert a blank column next to the dates column, then type a formula similar to this:
=IF(ISNUMBER(A2),A2,"") where A2 is the first date (or non-date).
Dates are numbers in Excel, so the formula tests for proper dates. For non-dates, the result cells will be blank. Run this formula down the column, then run a filter in that column to filter out blanks. Your result will be the proper date rows.
I hope this helps.
Kind regards
Marius
RE: Filter by cell format possible?
Hi Marius,
Thanks for the formula. I've tried it and it works well for the most part of the table although for some reason it is still registering pre-1900 years like 1663 as numbers and returning "1663" as a value even though I have manually formatted these types of cells as Text?
Many thanks!
Kind regards,
Isabel
RE: Filter by cell format possible?
Hi Isabel,
When you mention "1663", is that all that is in the cell? In other words, not e.g. 02/05/1663?
If so, you can convert these "dates" to text using:
=TEXT(A2,"#")
Then the IF and the filter should work.
Kind regards
Marius
