Excel won’t recognize dates

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:

lastlogon-unformatted

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:

  1. Select the whole column.
    • excel-select-column
  2. Click on Data on the top ribbon and then click Text to Columns.
    • excel-data-textTocolumns
  3. Select Delimited and click Next.
    • excel-textTocolumns-Delimited
  4. Now make sure that nothing is selected and click next.
    • excel-textTocolumns-delimiters
  5. Select Date and select DMY (or whatever format you have in your column) and then click Finish.
    • excel-textTocolumns-date-DMY

There you go:

excel-date-formatted-column

Leave a Reply

Your email address will not be published. Required fields are marked *