Filter date, showing all dates except 2018s

[lezawang]

Hi
I want to filter this table using Date. I do not want to see any date of year 2018. So what I did, select

Data Filter -->between

after 12/31/2018
before 1/1/2018

But excel showed 0 rows.

I tried AND/OR same result
I tried After 1/1/2018
Before 12/31/2018 , got same result

[TABLE="class: grid, width: 287"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]customers[/TD]
[TD]product[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]10\14\2018[/TD]
[TD]mini mart[/TD]
[TD]milk[/TD]
[TD]905[/TD]
[/TR]
[TR]
[TD]1\04\2017[/TD]
[TD]mega mart[/TD]
[TD]coffee[/TD]
[TD]946[/TD]
[/TR]
[TR]
[TD]1\29\2018[/TD]
[TD]food mart[/TD]
[TD]bread[/TD]
[TD]882[/TD]
[/TR]
[TR]
[TD]9\04\2016[/TD]
[TD]giant x[/TD]
[TD]tea[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]12\21\2016[/TD]
[TD]ABC store[/TD]
[TD]milk[/TD]
[TD]151[/TD]
[/TR]
[TR]
[TD]10\01\2018[/TD]
[TD]xyz inc[/TD]
[TD]coffee[/TD]
[TD]737[/TD]
[/TR]
[TR]
[TD]2\17\2016[/TD]
[TD]mini mart[/TD]
[TD]tea[/TD]
[TD]484[/TD]
[/TR]
[TR]
[TD]8\31\2016[/TD]
[TD]ABC store[/TD]
[TD]coffee[/TD]
[TD]631[/TD]
[/TR]
[TR]
[TD]1\01\2017[/TD]
[TD]giant x[/TD]
[TD]milk[/TD]
[TD]855[/TD]
[/TR]
[TR]
[TD]8\17\2017[/TD]
[TD]mini mart[/TD]
[TD]milk[/TD]
[TD]975[/TD]
[/TR]
[TR]
[TD]6\13\2016[/TD]
[TD]mega mart[/TD]
[TD]coffee[/TD]
[TD]255[/TD]
[/TR]
[TR]
[TD]7\03\2018[/TD]
[TD]food mart[/TD]
[TD]bread[/TD]
[TD]184[/TD]
[/TR]
[TR]
[TD]7\16\2016[/TD]
[TD]giant x[/TD]
[TD]tea[/TD]
[TD]704[/TD]
[/TR]
[TR]
[TD]10\23\2017[/TD]
[TD]ABC store[/TD]
[TD]milk[/TD]
[TD]951[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

It would be before the first day of 2018 not the last so before 1/1/2018.

 

[lezawang]

sorry i meant 2017s year. I tried this:

After 31/12/2017
Before 1/1/2017

Still did not work, excel hided all the rows!

 

[steve the fish]

Ok having another look your dates arent dates. Test one of the date cells with ISNUMBER. Do the say true or false. Lets say a date is in A2:

=ISNUMBER(A2)

 

[lezawang]

Yes are number. I used =today()-randbetween(1,1000) to generate these number then Copy-->Paste value only to get dates only. Thank you.

 

[steve the fish]

If they are dates an autofilter should give you a date filter. Just uncheck 2017.

 

[lezawang]

Yes it did work if i select 2017 but I wanted to learn how to use Between after date, and before date. Thank you.

 

[steve the fish]

You would have to do is before 01/01/2017 OR is after 31/12/2017