How to Change Date Format in Excel

When you enter a date into Microsoft Excel, the program will format it according to the default date settings. For example, if you want to enter the date February 6, 2020, the date could appear as 6-Feb, February 6, 2020, 6 February, or 02/06/2020, all depending on your settings. You may find that if you change a cell’s formatting to “Standard,” your date becomes stored as integers. For example, February 6, 2020 would become 43865, because Excel bases date formatting off of January 1, 1900. Each of these options are ways to format dates in Excel. To help with organizing data in Excel, learn about how to change the date format in Excel. 

Choosing from the Date Format List

Formatting dates in Excel is easiest with the date formats list. Most date formats you may want to use can be found in this menu. 

How to Change The Excel Date Format

1. Select the cells you want to format
2. Click Ctrl+1 or Command+1
3. Select the “Numbers” tab
4. From the categories, choose “Date”
5. From the “Type” menu, select the date format you want

Creating a Custom Excel Date Format Option

To customize the date format, follow the steps for choosing an option from the date format list. Once you’ve selected the closest date format to what you want, you can customize it and change it.

1. In the “Category” menu, select “Custom”
2. The type you chose earlier will appear. The changes you make will only apply to your customized setting, not to the default
3. In the “Type” box, enter the correct code to alter the date
4. If  you are trying to change the date display to DD/MM/YYYY, simply go to Format Cells > Custom
5. Next, Enter DD/MM/YYYY in the available space given.

Converting Date Formats to Other Locales

If you are using dates for several different locations, you might need to convert to a different locale: 

1. Select the right cell or cells
2. Hit Ctrl+1 or Command+1
3. From the “Numbers” menu, select “Date”
4. Underneath the “Type” menu, there’s a drop-down menu for “Locale”
5. Select the right “Locale” 

You can also customize the locale settings:

1. Follow the steps for customizing a date
2. Once you’ve created the right date format, you need to add the locale code to the front of the customized date format 
3. Choose the right locale codes. All locale codes are formatted as [$-###]. Some examples include:
   1. [$-409]—English, United States
   2. [$-804]—Chinese, China
   3. [$-807]—German, Switzerland
   4. Find more locale codes

Tips for Displaying Dates in Excel

Once you have the right date format, there are additional tips to help you figure out how to organize data in Excel for your datasets. 

• Make sure the cell is wide enough to fit the entire date. If the cell isn’t wide enough, it will display #####. Double click on the right border of the column to make your column expand enough to display the date correctly. 
• Change the date system if negative numbers appear as dates. Sometimes Excel will format any negative numbers as a date because of the hyphens. To fix this, select the cells, open the options menu, and select “Advanced.” On that menu, select “Use 1904 date system.” 
• Use functions to work with today’s date. If you want a cell to always display the current date, use the formula =TODAY() and press ENTER. 
• Convert imported text to dates. If you import from an external database, Excel will automatically register the dates as text. The display may look the same as if they were formatted as dates, but Excel will treat the two differently. You can use the DATEVALUE function to convert. 

Why Your Date Format May Not Be Having Issues Changing

There are many reasons why you might be experiencing issues changing the date format in Excel. Listed are a few common difficulties. 

• There could be text in the column, not dates (which are actually numbers).
• Dates are left-aligned 
• An apostrophe could be included in the date
• A cell may be too wide.
• Negative numbers are formatted as dates
• Excel TEXT function is not being utilized.

Even with correctly formatted dates and displays, organizing data in Excel can only work as well as the data does. Messy data won’t lead to insights during analysis, however, it’s formatted. 

Data Preparation with Excel

Formatting data, by doing things like formatting dates, is part of a larger process known as “data preparation,” or all of the steps required to clean, standardize, and prepare data for analytic use. 

While data preparation is certainly possible in Excel, it becomes exponentially more difficult as analysts work with larger and more complex datasets. Instead, many of today’s analysts are investing in modern data preparation platforms like Designer Cloud to accelerate the overall data preparation process for data big or small.  

Schedule a demo of Designer Cloud to see how it can improve your data preparation process, or try the platform for yourself by getting started with Designer Cloud today.