Home > Skills > Data Entry > Fix Dates

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 sequential serial numbers. The imported dates are probably stored as a text value, instead of real numbers, and that is causing the formatting problem.

Note: Only positive numbers can be formatted as valid dates. If you format negative numbers as dates, the cells will be filled with number signs.

Excel Date System

There are date system differences in some versions of Excel.

Windows

• The date system in Microsoft Excel for Windows starts on January 1, 1900.

Excel for Mac

• Up to and including Excel 2008, the date system started on January 1, 1904.
• Since Mac Excel 2011 it is in line with the Windows date

See Numbers in Date Format

• 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 Dates - Text to Columns

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, all at once, so they become real numbers, is to use the Excel Text to Columns feature.

Start Text to Columns

To try this method for fixing text dates, follow these steps:

• First, select the cells that contain the dates you want to fix
• Next, on the Excel Ribbon, click the Data tab
• In the Data Tools group, click Text to Columns

Convert Text to Columns Wizard Step 1

• 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

Convert Text to Columns Wizard Step 2

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

Convert Text to Columns Wizard Step 3

In Step 3, you'll select a column in the sample data preview, and tell Excel how to convert it.

Choose Current Data Format

First, you'll tell Excel which format the dates are currently in. Later, after the dates are converted, you can choose a different date format on the worksheet.

Follow these steps to pick the current date format:

• First, in Step 3, check the Data Preview pane, at the bottom of the window
• There should be 3 columna, with the Date column highlighted in black background
  • If it is not highlighted, click anywhere in the Date column to select it
• After that, look at the options in the Column Data Format section, at the top of the window.
• Find the Date option in the list of data format settings
• Tip: You don't have to click the radio button at the left - it will be automatically selected later
• Next, click the Date drop down arrow at the right, to see the date format options
• In the drop down list, click on the date format that your dates are currently displayed in.
  • In this example, the dates are in month/day/year format, as shown in the Preview window
  • So, I selected the matching format for that date structure -- MDY
• In the Preview pane the column heading will change, to match your date format selection
• Also, the radio button for Date is automatically selected

Delete Columns Not Needed

Next, you'll delete any extra columns from the Data Preview pane.

• In this example
  • Ttime and AM/PM columns are not needed - we just want the date from the original data
    • Also, 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 extra columns from being included, follow these steps:

• In the Preview pane, click on the Time column, to select it
• In the Column data format section, click the radio button for “Do not import column (skip)”
• Repeat those steps, to select and skip an other extra columns

Finish the Date Fix

To complete the date conversion, follow these final steps:

• The Destination box shows the address of the first cell in the range of dates
  • Leave that address as it is
• Finally, in Step 3, click the Finish button, to convert the text dates to real dates
• The Convert Text to Columns Wizard window closes, and the worksheet dates should be real dates, instead of text 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 shown

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

Option 1 - Quick Format

• 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

Option 2 - Format Cells

• Select cells with fixed dates, and go to the Home tab on the Excel Ribbon
• 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

Option 3 - Custom Format

Follow these steps to create a custom date format:

• Select cells with fixed dates, and go to the Home tab on the Excel Ribbon
• Click the dialog box launcher, at the bottom right of the Number group on the Ribbon, to see more formats
• In the Format Cells dialog box, 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-Feb-2023
  • type ddd mmmm dd for a date with 3-character day name, full month name and 2-digit day number: Thu February 21
  • NOTE: There are more examples of Custom number formatting on the Microsoft website

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

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