How to fix Excel dates that won't change format, by using a built-in Excel tool.
NOTE: To follow along with the steps in this tutorial, download the sample Excel Dates Fix Format workbook.
In the screen shot below, Column C contains imported dates, which show the date and time, separated by a space character.
The Short Date format has been applied, from the Excel Ribbon, but the date format does not change. Those dates stay in the same date/time format.
In Excel, dates are stored as numbers -- number 1 is the date January 1, 1990. You can format positive numbers in one of the date formats, to see the date that they represent.
NOTE: The latest date allowed for calculation in Excel is 12/31/9999, which is the number 2958465
Although the entries in column C look like dates, Excel sees them as text, not real dates, and Excel will not apply number formatting to text.
There are a few signs that the items in column C are being treated as text:
If you want to sort the dates in column C, or change their format, the text dates have to be converted to real dates (numbers).
A quick way to fix the "text" dates is to use the Text to Columns feature -- follow these steps:
On the worksheet, check the items in column C, to see if they are real dates (numbers) now. There are a few signs that the cell contents are now being recognized as real dates:
After the dates have been converted to real dates, you can format them with the Number Format commands.
To format the dates, select them, and use the quick Number formats on the Excel Ribbon
Click the dialog launcher, at the bottom right of the Number group on the Ribbon, to see more formats.
The date formatting should work correctly, after you have converted the text dates to real dates.
To try the steps in this tutorial, download the sample Excel Dates Fix Format workbook. The file is zipped, and is in xlsx file format, The file does not contain any macros.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: May 2, 2018 3:02 PM