Excel Dates How to Fix Format

If you import data into Excel, and try to format a column of dates, sometimes the dates will not change format. See how to fix Excel dates that won't change format, by using a built-in Excel tool, and a few simple steps.

Problem Excel Dates

If you import data into a Microsoft Excel spreadsheet, and try to format a column of dates, sometimes the dates will not change format. This video shows how to fix the dates that won't change format, with a few simple steps. There are written steps below the video, and to follow along with the steps in this video, download the sample Excel Dates Fix Format workbook. Video Timeline 0:00 Introduction 0:12 Dates Won't Change Format 0:46 Text to Columns 1:03 Step 2 1:11 Step 3 1:47 Check the Dates

Excel Dates Do Not Change Format In the screen shot below, Column C contains imported dates, which show the date and time, separated by a space character. The date is in short date format - d/m/yyyy. The time shows hour, minute and second = hh:mm:ss with AM or PM. Using the Number format commands on the Excel Ribbon Home tab, the Short Date format has been applied, from the number format menu. However, the date format for the imported dates in column C does not change. Those dates stay in the same date/time format, no matter how you try to format cells that contain those dates. Dates Are Numbers Why won’t the imported dates change format? Why won't they show a short date, without time included, in the cell's formatting? In Excel, dates are stored as numbers. The imported dates are probably stored as text, instead of real numbers, and that is causing the problem. Excel Date System The date system in Microsoft Excel for Windows starts on January 1, 1990. Note: In Excel for Mac, the date system starts on January 1, 1904 If you type the number 1 in an Excel worksheet cell, then point to the Short Date format on the Excel Ribbon, you can see that date in the formatting preview -- 1/1/1900. Below that, the Long Date format is showing the weekday name, and full month name for the date --Sunday, January 1, 1900 Excel Date System Notes Here are a few notes on formatting numbers as dates: In Excel, you can format positive numbers in any of the date formats, to see the date that they represent. The number zero is formatted as January 0, 1900, which is calculated as December 31, 1899 The latest date allowed for calculation in Excel is 12/31/9999, which is the number 2958465 Learn more about the Excel date systems on the Microsoft website. Problem Date Previews However, if you select a cell with one of the imported dates that won't change format, the Number format commands show that there is a problem: Short Date preview just shows the cell contents. It does not show a Short Date format. Long Date format does the same thing -- it just shows the cell contents. Imported Dates Are Text Data Although the entries in column C look like dates, Excel sees them as text, not real dates. And that's why the imported dates won't change format -- Excel will not apply number formatting to text. Here are a few more signs that the items in column C are being treated as text: The items are left-aligned -- dates (numbers) are right-aligned by default There is an apostrophe at the start of the date (visible in the formula bar) If two or more dates are selected, the Quick Calc in the Status Bar only shows Count, not Numerical Count or Sum.

Fix the Dates

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:

• Select the cells that contain the dates
• On the Excel Ribbon, click the Data tab
• Click Text to Columns

• The Convert Text to Columns Wizard opens
• In Step 1, select Delimited -- our items have a space character that separates the date and time,
• Click the Next button

• In Step 2, select Space as the delimiter -- there is a space between the date and the time in each cell
• The preview pane should show the dates divided into columns.
• Click Next

• In Step 3, in the preview pane, click on the date column, and select Date
• In the Date drop down, choose the date format that your dates are currently displayed in. In this example, the dates show month/day/year, so MDY was selected.

• In this example
  • the time is not needed, and we don't want to overwrite the data in column D and column E.
  • there is a blank column before the date -- that is not needed
• To prevent the time from being included, select each of the remaining columns (1, 3 and 4), and set it as “Do not import column (skip)”
• Click the Finish button, to convert the text dates to real dates

Check the Results

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:

• The items are right-aligned
• There is no apostrophe at the start of the date (visible in the formula bar)
• If two or more dates are selected, the Quick Calc in the Status Bar shows Count, Numerical Count and Sum.

Format the Dates

After the dates have been converted to real dates, you can format them with the Number Format commands.

• Note: Default date formats in Excel are affected by the Windows regional settings on your computer. To use a different locale, go to the Microsoft website for steps on how to open the control panel and change your regional settings.

To change date formats, follow the steps below -- 3 different options are show:

• Select cells with fixed dates, and use the quick Number formats on the Excel Ribbon
• For example, click the Short Date format or Long Date format, to apply date formatting quickly

OR

• Click the dialog launcher, at the bottom right of the Number group on the Ribbon, to see more formats.
• In the Format Cells dialog box, select the Number tab, if it is not selected automatically
  • TIP: You can use a keyboard shortcut to open the Format Cells dialog box:
    • Press Ctrl + 1 (one)
• At the left, in the Category list, click on the Date category
• In the centre of the dialog box, in the Type list, you'll see a list of built-in Excel date formats
• In that list, click on the date format that you want for the fixed dates
  • Some formats will show a single digit for day or month, when applicable, e.g. 6/7/22
  • Other formats will always show 2 digits for day or month, e.g. 06/07/22
• Click the OK button, to apply the selected format

OR

• At the left, in the Category list, click on the Custom category
• In the centre of the dialog box, in the Type box, type the text string that represents the custom date format you want to use
• For example, try one of these text strings for the custom number format setting,
  • type dd-mmm-yyyy for a date with a two-digit day, short month name, and 4-digit year: 21-Mar-2022
  • type ddd mmmm dd for a date with 3-character day name, full month name and 2-digit day number: Mon March 21
  • NOTE: There are more examples of Custom formats on the Microsoft website

In the screen shot below, the pointer is over the dialog launcher for the Number group on the Ribbon

• 

All of the built-in date formatting options should work correctly, after you have converted the text dates to real dates.

Get the Sample File

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.

Related Tutorials

Functions List

Change Text to Numbers

Excel Date Functions

Date Picker

Dates and Times FAQs