The Excel date format not changing is one of the most common queries we get in our Excel classes i.e. it won’t change the format from 17/05/2015 to 17 May 2015 no matter how hard you click the button!
The simple answer is that if the format does not change, the cell you are looking at is NOT a date (according to Excel anyway). In fact this process of trying to change the format of a date is what we use to test whether Excel is seeing a date properly.
We need to step back a moment to understand how dates work in Excel.
If you have a valid date and you format it as a general number you will see something like 39 987. This is what Excel uses as a date and it means that the date is 39 987 days from the 1st January 1900. The fact that you format it into a proper date format is only for humans benefit. Excel is happy with the number. In fact it has to have a number!
If Excel doesn’t recognise the date as a date (e.g. I use American format of mm/dd/yyyy instead of what my computer needs of dd/mm/yyyy) it becomes text and no matter what you do it will not change the format or allow any date functions to work.
Before you can change the format on a date or use any of the date functions you need to convert it to a valid date. There are many ways to do this covered in the Handling Dates section including how to change American style dates (mm/dd/yyyy) to European style (dd/mm/yyyy) in one or two clicks.