Date Filter not working

I have created a work book with 3 years worth of information. For some reason recently the dates aren't filtering correctly. The cells are formatted as "Short Date" and the cell inputs appear to be correct.

When I filter from newest to oldest, it sorts the entries in the following order.

30/12/2015
30/12/2014
30/11/2016
30/11/2015
30/11/2014
30/10/2016
30/10/2015
30/10/2014
30/09/2016

I have copied and pasted the original content from my workbook to a new spreadsheet (See attached). If somebody could take a look and let me know what has gone wrong it would be much appreciated.

Thank you for your reply, I don't understand why it works for you and not for me. These are the steps I follow.

1. I download the spreadsheet
2. In the filters at the top, I click to filter "Start Date"
3. The options that are listed in the filter are the years, 2017, 2016, 2015, 2014, 2013 but then there are additional dates listed such as 07/01/16 which shouldn't be listed in the filter as it should be included in the 2016 field.
4. Anyway, I then select "Sort Oldest to Newest but the dates are clearly not in date order.

Start Date
30/12/2015
30/12/2014
30/11/2016
30/11/2015
30/11/2014
30/10/2016
30/10/2015
30/10/2014
30/09/2016
30/09/2015
30/08/2016
30/08/2015
30/08/2014
30/07/2015
30/07/2014
30/06/2016
30/06/2015
30/06/2014
30/05/2016
30/05/2014
30/04/2016
30/04/2015

Hi,

Do one thing as use find replace "/","-" and sort it again it may work .

Punnam

no need to sort

Punnam

Thanks Punnam,

I have noticed that before making the change (Replacing "/" with "-") it appeared to be sorting in the order by month rather than year. See below.

30/12/2015
30/12/2014
30/11/2016
30/11/2015
30/11/2014
30/10/2016
30/10/2015
30/10/2014
30/09/2016
30/09/2015
30/08/2016
30/08/2015
30/08/2014
30/07/2015
30/07/2014
30/06/2016
30/06/2015
30/06/2014
30/05/2016
30/05/2014
30/04/2016
30/04/2015
30/04/2014
30/03/2016
30/03/2015
30/01/2017
30/01/2016
30/01/2015

When I replaced the / with a - this seems to have resolved the problem.

Many thanks

Chris

hi,

Check this file

Punnam

Many thanks Punnam,

Your additions are very interesting. I am puzzled as to how the ISNUMBER function has reported "TRUE" for some dates and "FALSE" for others. Replacing the / with a - clearly resolves the problem.

Many thanks for your help, I have now managed to fix the problems with my original spreadsheet

Hi Chris,

u are welcome , so u r query was answered . mark the thread as solved using thread tools .
U can even thank me by adding some reputation point i.e the "Star" button on left side of my post .

Punnam