Contextures

Excel Dates Fix Format

How to fix Excel dates that won't change format, by using a built-in Excel tool.

Problem Excel Dates

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.

problem dates

Dates Are Numbers

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:

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

Quick Calc in the Status Bar

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

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 Next

select Delimited

  • 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

select Space as the delimiter

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

choose the date format

  • 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 Finish, to convert the text dates to real dates

Do not import

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.

Check Status Bar

Format the 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

OR

Click the dialog launcher, at the bottom right of the Number group on the Ribbon, to see more formats.

Format the numbers

The date formatting should work correctly, after you have converted the text dates to real dates.

real dates with different format

Download 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

Don't Miss Our Excel Tips

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.

Get weekly Excel tips from Debra

 

 

 

30 Excel Functions in 30 Days

 

Get weekly Excel tips from Debra

 

 

excel tools

 

Last updated: May 2, 2018 3:02 PM