Help, Excel won’t recognize dates! This is frustrating sometimes, even when you right click on a cell and give it the correct format. In my case I have a script that exports the last logon dates from Active Directory and they’re formatted as DD/MM/YYYY. So for instance the 25th of March 2016 would be 25/03/2016. Very simple!
When trying to filter it, you’ll see them all in there and no Data Filtering is available:
The funny thing is.. I also have a last password set field which is formatted the same way and that works just fine. I’m assuming the 01/01/1601 is driving Excel mad? 🙂
Either ways, this workaround I found seems to work all of the times:
- Select the whole column.
- Click on Data on the top ribbon and then click Text to Columns.
- Select Delimited and click Next.
- Now make sure that nothing is selected and click next.
- Select Date and select DMY (or whatever format you have in your column) and then click Finish.
There you go: