How to Change Date Format in Excel - Examples (2024)

Home > Format > Date > Dates

If an Excel worksheet contains dates, how can you change the date format? For example, if dates are in m/d/yyyy format, how can you change them to yyyy-mm-dd? See how to make a quick change to the date format, or use custom date formatting to get exactly what you need.

Excel Dates Are Numbers

In Microsoft Excel, valid dates are between:

• January 1, 1900 and December 31, 9999.

Excel stores dates as numbers, and those numbers can range from:

• 1 (Jan 1, 1900) to
• 2,958,465 (Dec 31, 9999)

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

How to Change Date Format in Excel

To change the date format for cells in an Excel worksheet, use one of the following 3 methods.

Click on a blue link, to go to the details:

1. Quick Format: This is an easy way to change the date format. Choose from 2 options - Short Date format or Long Date format.
2. Format Cells Dialog: This method gives you a longer list of choices for date formatting. Choose one of the 18 built-in date formats
3. Custom Format: Create exactly the Excel date format that you need, with any combination of weekday name, day number, month, and year.

1) Quick Date Format

Here's a quick way to change the date format for one or more cells, using a drop-down list on the Excel Ribbon:

• First, select one or more cells that contain valid dates, or positive numbers
• On the Excel Ribbon, go to the Home tab
• In the Number group, click the drop-down arrow on the list of number formats

Choose a Format

• A long list of number formats appears, and there are two date formats, with previews for how they format the date in the active cell
  • Short Date
  • Long Date
• Click one of the date formats, to apply that format to the selected cells

Note: See the Quick Format tips in the section below

Quick Format Tips

Here are a couple of tips for using the quick date format options on the Excel Ribbon.

Number Format Previews

For each number format in the list, Excel shows a preview for the active cell on the worksheet. Some of the number formats are based on your computer system settings.

• The two date options use the default date formats from your computer's regional settings.

On my computer, the short date format is mm/dd/yyyy, so that's what the number format preview shows.

Change Regional Settings

If you aren't sure how to change your regional settings in the Control Panel, go to the Microsoft site for details.

Note: If you're using Excel at work, you might need to consult with the system administrator before you change the Regional Settings.

2) Format Cells Dialog

If you don't want to use the default Short Date format or Long Date format, follow the steps below, to see a longer list of date formatting options.

Open Format Cells Dialog Box

• First, select one or more cells that contain valid dates, or positive numbers.
• Next, use one of the following methods to open the Format Cells dialog box:
  • a) Keyboard Shortcut
    • Press Ctrl + 1 (one)
    • Note: Use the 1 key on the main keyboard, NOT the number pad
  • b) Ribbon Command
    • On the Excel Ribbon, go to the Home tab
    • In the Number group, at the bottom right corner, click the dialog box launcher

Choose Built-In Date Format

When the Format Cells dialog box opens, follow these steps to choose a Date format for the selected cells:

• At the top of the Format Cells dialog box, select the Number tab, if it was not selected automatically
• At the left, in the Category list, click on the Date category
• In the centre of the dialog box, in the Type list, Excel shows a list of built-in Excel date formats
  • (optional) In that list, click one or more date formats, to see a sample of how the active cell will look in each format.
• Next, in the list, click on the date format that you want to apply
  • Note: Two of the date formats include the time settings (hour and minute). If the cell does not contain a decimal number, or a date and time, the hour will show as 12 midnight.
• Finally, click the OK button, or press Enter, to apply the selected Date format, and to close the dialog box

Note: See the Date Format Selection Tips in the section below

Date Format Selection Tips

Here are a few tips about Date formats in the Format Cells dialog box.

a) Check the Sample

Before you select a date format and click OK, you might find it helpful to look in the Sample box, above the list.

For example, in the animated screen shot below, there are 2 date formats that look identical. However:

• When I click on one format, it shows the day number as a single digit.
• With the other format, it shows the day number with a zero placeholder.

I like that zero placeholder in some worksheets, to help keep the dates lined up nicely, in a column.

b) Regional Settings

• The first two formats, that begin with an asterisk (*), are based on the Regional Settings on your computer.
• Note: Cells with those (*) formats will automatically change, if the Regional settings are changed.

c) Choose Different Locale

• To override the system's default formats, you can choose a different location from the Locale drop down list.
• Changing the Locale does NOT change your Regional settings -- it only overrides those format settings, in the workbook.

Note: The selected Locale might affect the following options:

• number/date formatting in the Quick Number format drop down list.
• built-in date formats in the Format Cells Date category.

3) Custom Date Format

If none of the built-in date formats are what you need, follow the steps below, to create a custom date format,

Get Started

• First, select one or more cells that contain valid dates, or positive numbers
• Next, press Ctrl + 1 to open the Format Cells dialog box
• (optional) At the left, in the Category list, click Date, then click on a format that is close to what you want.
  • This step is optional, but I find it easier than starting a custom format from scratch.
• Next, in the Category list, click on Custom
• In the Type box, if you previously selected a built-in date format, you'll see the custom number code for that format.

Check the Type Box

In the screen shot below, I had selected the Mar-12 built-in date format. In the Type box, that code was automatically entered:

• [$-en-US]mmm-yy;@a

This date format string has 4 pieces of information:

• locale code at the beginning ([$-en-US])
• letters that set the date format:
  • mmm string represents a 3-letter month name code (Jan)
  • yy string represents a 2-digit year number (00)
• formatting code for text entries in the cell -- ;@

Note: For details on the sections in custom number format codes, see the guidelines on the Microsoft website.

General Format

If you did not select a date format, you'll probably see "General" in the Type box. That's the default number format for worksheet cells.

• General is not a date format, as you can see in the Sample box, where the number (1) is showing

Create Custom Format Code

In the Custom format Type box, you can:

-- a) Modify a built-in date format's code

-- b) Build a custom format code from scratch

Click the blue links, to go to the instruction for each method below.

a) Modify Built-In Date Format

If you selected a built-in date format, you can modify that code in the Type box, to create the custom format that you need.

• For example, I wanted a 4-digit year, instead of a 2-digit year.
• In the Type box, changed "yy" to "yyyy".

Tip: You can also scroll through the list of custom formats, and click on a date format that you want to use or modify.

System Date Code

If you try to modify a date format that begins with a system date code, [$-x-sysdate], your changes will have no effect, as shown in the screen shot below.

• I changed the day code to ddd, and the month code to mmm.
• However the full weekday and month names still show in the Sample box.

Important: You must delete the [$-x-sysdate] prefix, so your changes will be recognized.

b) Create Code From Scratch

If you did not select a built-in date format, you can create the custom number format that you need for your date cells.

Tip: See the next section for more examples of Custom Date Format codes

• First, clear out any text that is in the Type box (such as "General")
  • Tip: You can scroll through the list of custom formats, and click on a date format that you want to use or modify
• Next, type the text string that represents the custom date format you want to use. For example:
  • Type dd-mmm-yyyy
    • 21-Feb-2023
  • Type ddd mmmm dd
    • Thu February 21

Watch the Sample Box

As you type the date format code, check the Sample box, to see how your custom date format will look. For example, in the animated screen shot below, I typed the code: yyyy-mm-dd

There are more examples for Custom Date Format codes in the section below.

More Custom Date Format Codes

In this section, there are more examples of custom date format codes that you can use in the Type box.

Day Month Year

First, this table shows the codes that you can use for Day, Month and Year in the date format text string code.

• In column A, I've entered day, month and year codes, from 1 to 5 repetitions.
• In column B, the item type is listed (day, month or year)
• In column C, the results are shown for Friday, May 3, 2024
• In column D, the results are shown for Monday October 14, 2024

Notes:

1. For the May dates, there is a zero placeholder for the codes dd, and mm
2. Although you can use yyy is a date format, it shows a 4-digit year -- the same number of digits as the yyyy code
3. Code "mmmm" shows full month names. Because May is only 3 characters, the result is the same as "mmm"

Built-In Date Format Codes

In the next table, below, I've listed the codes for the 18 different Date formats that are listed in the Date category, when my Regional settings are English - United States.

Notes:

• Formats 13 and 14 include the time with the date.
• Built-in date list might change, for different regional settings, or if a different locale is selected. For example, I only see 8 built-in date formats, instead of 18 formats, when my Regional settings are English - Canada.

Date Format Separators

In the list shown above, there are date formats with 4 different types of separator characters:

• forward slash (/)
• hyphen (dash) (-)
• space character
• colon (:)

Other Separators

You can use other types of separators, if you use a backslash before the character. For example:

• I like to use this format, especially for naming files: yyyy_mm_dd
• This format makes it easy to sort things alphabetically, in order of year, month and day.

Backslash Example

However, if I type yyyy_mm_dd in the Custom Format Type box, the underscores are shown as spaces.

To get a date format with underscores, I type the following code, with a backslash before each underscore:

• yyyy\_mm\_dd

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

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.

Date Cell Filled with Number Signs

Sometimes when you're working with date functions, the formula cell shows all number signs (hash tags, pound signs) instead of a number or date.

Cell Too Narrow

In some cases, this happens because the cell is too small to show the date.

To fix that problem, make the column wider, so the formatted date will fit.

Negative Numbers

Another reason that date cells are filled with number signs is that a formula returned a negative number, and the cell is formatted as a date:

• Excel dates must be zero or greater.
• Excel dates cannot be negative numbers.

For example, in the screen shot shown below, the formula cell C4 shows all number signs, instead of a number of days between the start date and end date.

This happened because:

• I accidentally subtracted the later date (B4) from the earlier date (A4).
• The result is a negative number
• Cell C4 was formatted as a short date

Video: 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, like the dates shown below.

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

This video shows how to fix the dates that won't change format, with a few simple steps. There are written steps on the How to Fix Dates That Won't Change Format page.

Get the Sample File

To try the steps in the Date Fix video, download the sample Excel Date Fix Format workbook. The file is zipped, and is in xlsx file format, The file does not contain any macros.