How to Sort by Date on "blank cells"?

[ImpetuousRacer]

Ok, so I have data that is all dates in column A that pulls from another source:

I used this formula in column B to suppress errors:

=IF(ISERROR(VALUE(A1)),"",VALUE(A1))

Therefore, This formula shows a blank cell if there is an error.

However, I noticed when I need to sort the data in column B using a filter, it only allows me to sort A to Z and not from "Oldest to Newest". Ive highlighted all of column B, and made sure the formatting of the cells is set to Date.

In Column C, I put the formula =ISTEXT(B1) and so forth, and it shows False for cells that are truly blank, but True for blank cells created from the formula that used "", even though I have that cell set as a Date. If I use nothing in the ISERROR formula instead of blank, I get the date of 1/0/1900 which I would like to avoid as when sorting it puts all these dates to the top.

Is there another method to have a blank and override so Excel 2007 sees it as date and not text? Thanks for the help.

Oh, and another thing to note is that in column B, when I have 10 dates, I can have 15 more cells blank from the iserror fomula, but at number 16 of blank results it will change the ability from oldest to newest sorting to A to Z.

 

[ajetrumpet]

racer,

not sure if I have an answer, but something you might want to know is that "" is not BLANK. It is a 0-length string, which is considered text I believe. Furthermore, I don't think BLANKS or NULLS are considered text. There might also be an issue with the DATE type, as I believe it completely ignores 0 lengths, NULLs and BLANKS. e.g. - the value is only a date if there is something present in the cell.

One thing to consider is a block of code that converts all your dates to text after import. Another alternative would be to switch the format of the date to "yyyy/mm/dd", which would always sort correctly regardless of the data type (almost sure, but not 100%)

 

[ImpetuousRacer]

Thanks ajetrumpet for the help and the reasoning of everything. Much Appreciated! Using the date format "yyyy/mm/dd" will work for my sorting needs.

 

[ajetrumpet]

Thanks ajetrumpet for the help and the reasoning of everything. Much Appreciated! Using the date format "yyyy/mm/dd" will work for my sorting needs.

great! good luck!