There’s a video at the end of this article, that shows how to do that. Sometimes, you can fix the dates by copying a blank cell, then selecting the date cells, and using Paste Special > Add to change them to real dates.
If you want to sort the dates, or change their format, you’ll have to convert them to numbers – that’s how Excel stores valid dates. If two or more dates are selected, the Quick Calc in the Status Bar only shows Count, not Numerical Count or Sum.There is an apostrophe at the start of the date (visible in the formula bar).There are a few signs that the cell contents are being treated as text: Why won’t the dates change format? Even though they look like dates, Excel sees them as text, and Excel can’t apply number formatting to text. I didn’t want the times showing, but when I tried to format the column as Short Date, nothing happened – the dates stayed the same. In the screen shot below, you can see the column of imported dates, which show the date and time. This video shows how to fix the dates that won’t change format, and there are written steps below. That happened to me this week, and here’s how I fixed the problem, using a built-in Excel tool. Have you ever imported data into Excel, from your credit card statement, or somewhere else, and found that Excel dates won’t change format? And, if you try to sort that column of dates, things end up in the wrong order.