Home > Formulas > Date > 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. In my short video, I show how to fix Excel dates that won't change format, step by step. There are written steps too, and a sample Excel file to download Tip: Find more information on how to change date format in Excel.

A. Excel Dates Do Not Change Format

If you import data into a Microsoft Excel spreadsheet, and try to format a column of dates, sometimes the dates will not change format.

• Video: First, this video shows an example of dates that won't change format, and how to fix the problem, with a few simple steps. To follow along with the steps in this video, download the sample Excel Dates Fix Format workbook.
• Written Steps: Below the video, there are written steps that show the problem, why it occurs, and how to fix it quickly.

Video Timeline

• 0:00 Dates Won't Change Format
• 0:46 Fix With Text to Columns
• 1:47 Check the Dates

A.1. Example - Problem Excel Dates

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.

A.2 Can't Change Date Format

With this sample data, I didn't want to see the time in that column. The times were all "midnight", and added clutter. Here are the steps I tried, to change the date format:

• First, I went to the Home tab on the Excel Ribbon
• Next, in the Number group, I clicked the arrow in the Number format drop down
• Then, in the list of number formats, I clicked on the Short Date format

Unfortunately, nothing happened. Those dates stayed in the same date/time format, no matter what number format I tried to apply.

A.3 Why? Numbers Vs Text

In the example shown above, why won’t the imported dates change format?

• Numbers: In Excel, dates are stored as sequential serial numbers. Numbers can be changed to a different number format.
• Text: The imported dates are probably stored as text values, that look like dates. Text values cannot be changed to a different number format.

In the section below, I show a quick way to change text values to real dates.

B. 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).

• In the steps below, see how to fix the "text" dates, all at once, by using the Excel Text to Columns feature.
• After the dates are fixed, you can change their number format.
  • I show those steps in section C.

B.1 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

B.2 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

B.3 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

B.4 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.

B.4.1 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 columns, 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

B.4.2 Delete Columns Not Needed

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

• In this example
  • Time 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

B.4.3 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

B.5 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.

C. Change Date Format - "Real" Dates

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

To change date formats, follow the steps in one of the 3 different options below.

Tip: Find more information on how to change date format in Excel.

C.1) 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

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.

C.2) 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
• Finally, click the OK button, to apply the selected format

C.3 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 to select 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 Change Date Format page

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

Get the Excel Files ✅

• Fix 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.

🐰 Down the Rabbit Hole 🐰

In this section, you'll find related Excel tips, shortcuts, skills, and details. These things are nice to know, but not essential, so you can skip this section, and get back to work. Or dive in, for some extra Excel fun and knowledge!

--a) Check for Text "Dates"

--b) Excel Date System

--c) No Negative Numbers

a) Check for Text "Dates"

Here are a few ways to check for cells that contain text values, instead of real dates or numbers.

Number Format Drop Down

If you select a cell with a date that won't change format, the Number format commands usually show that there is a problem.

In the example shown below:

• 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.

More Ways to Check for 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.

b) 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 those numeric values 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.

c) No Negative Numbers

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

Get Monthly Excel Tips! Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up. Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

Related Tutorials

Dates, Change Format

Excel Date Functions

Date Picker

Dates and Times FAQs

Change Text to Numbers

Functions List