Excel does not recognise dates

A common issue when importing and working with data is that Excel seems to have problems with noticing what is a date and what isn’t a date. The key is to understand how Excel sees dates and then what needs to be done to convince Excel that it is looking at dates.

Firstly it is important to realise that the format of the date (is it 25 Jan 2015 or Jan 25 2015 or 25/1/2015 or 2015/25/1) is not there for Excel’s benefit. Formatting is only for the users benefit. All the above formats are seen exactly the same to Excel i.e. 42029. Excel basis all dates on the 1st Jan 1900 so the above dates are 42 029 days after the 1 Jan 1900.

If Excel recognises that a cell contains a date, you should be able to format the date into any format you want including as a number. If you can’t change the format of the date it is NOT A VALID Excel date no matter how much it looks like it to you. As far as Excel is concerned it is text.

Back to: