How to Change Date Format in Excel
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:
Excel dates are stored as numbers, which can range from
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.
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 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:
Choose a Format
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.
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
Choose Built-In Date Format
When the Format Cells dialog box opens, follow these steps to choose a Date format for the selected cells:
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:
I like that zero placeholder in some worksheets, to help keep the dates lined up nicely, in a column.
b) Regional Settings
c) Choose Different Locale
Note: The selected Locale might affect the following options:
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,
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:
This date format string has 4 pieces of information:
Note: For details on the sections in custom number format codes, see the guidelines on the Microsoft website.
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.
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.
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.
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
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.
|Code||Item||May 3, 2024||Oct 14, 2024|
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.
|ID||Code||May 3 2024|
|2||dddd, mmmm dd, yyyy||Friday, May 3, 2024|
|12||mmmm d, yyyy||May 3, 2024|
|13||m/d/yy h:mm AM/PM||5/3/24 12:00 AM|
|14||m/d/yy h:mm||5/3/24 0:00|
Date Format Separators
In the list shown above, there are date formats with 4 different types of separator characters:
You can use other types of separators, if you use a backslash before the character. For 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:
Excel Date System
There are date system differences in some versions of Excel.
Excel for Mac
Excel Date System Notes
Here are a few notes on formatting numbers as dates:
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.
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:
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:
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.
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.
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.
Last updated: November 9, 2023 12:16 PM