See How Data Engineering Gets Done on Our Do-It-Yourself Data Webcast Series

Start Free

Speed up your data preparation with Trifacta

Free Sign Up
Summer of SQL

A Q&A Series with Joe Hellerstein

See why SQL is Back
All Blog Posts

How to Change Date Format in Excel

November 2, 2020

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. 

  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

excel date format

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. 

  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

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

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 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 Trifacta to accelerate the overall data preparation process for data big or small.  

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