Ändern des Datenformats 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 in either long date format, or short date format, 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

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 text to dates. If you import from an external database, Excel will start auto-correcting data 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 
• Dates could be formatted as long date format when you want short date format or vice versa
• 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

Excel Date Formulas

Now that you reviewed how to change the date format, here are some helpful Excel formulas when calculating new dates:

• TODAY(): This formula gives you the current date which is automatically updated every day. To use this formula, you would just copy and paste the text ‘=TODAY()’.
• NOW(): This formula provides the current date and time, which updates automatically.
• DATE(year, month, day): Use this formula to create a date for dd mm yyyy (day, month, year) formatting when you want to combine year, month, and day into one cell.
• EDATE(start_date, months): This formula helps you find a date that is a certain number of months before or after a given date. To use this formula, you would use the start_date and the positive or negative number of months.
• EOMONTH(start_date, months): If you need to find the last day of a month, use this formula to include the start_date and include the number of months.
• WEEKDAY(serial_number, [return_type]): If you want to know the day of the week for a specific date, you can enter the date as the serial_number. The return type is optional in this formula and if omitted, will provide numbers 1 (Sunday) through 7 (Saturday).
• YEARFRAC(start_date, end_date, [basis]): This formula calculates the fraction of a year between two dates and can be used for things like age or length of service. To use this formula the start_date and end_date are required and the [basis] is optional.

An Easier Approach to 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. And, 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.

If you’re like most Excel users, you spend a lot of time doing data preparation – up to 80% of the time used for the entire analytics process. For most analysts, that time could be better spent building dashboards, thinking about the data insights, or communicating and distributing information across the organization.

For that reason, many of today’s analysts are investing in modern data preparation platforms like Alteryx [LB1] to accelerate the overall data preparation process for data, big or small. Alteryx makes it much easier to blend, join, transform, and standardize data from a wide variety of data sources – and it’s certainly a lot better than copying and pasting data between spreadsheets and hoping for the best. Using Alteryx, it just takes a few clicks to convert text, turn long date formats into short date formats, and a whole lot more.

To learn more, read our ebook, the Spreadsheet User’s Guide to Modern Analytics to discover how easy it is to switch from spreadsheets to a modern analytics approach.