Change date format during import in Excel
When importing data series, Excel likes to "unintentionally" convert numbers into the date format - but that can be changed. We show you two methods of how you can turn "May 5" back into "5.5".
Solution 1: change Excel cell format
The most obvious solution is via the "Format cells" menu.
- First select the cells in which the incorrectly displayed data is located.
- Then open the context menu with the right mouse button and select "Format cells" (see screenshot)
- In the new window, change the "Category" list from "Date" to "Number".
Solution 2: Import with the Excel text conversion wizard
Solution 1 does not help you if, for example, you import numbers with decimal places from a csv file that are not separated by commas, but in Anglo-American terms by a point - i.e. about 5.5 instead of 5.5, as is common in German. Here Excel automatically converts the numbers into the date format and cannot be dissuaded by another cell format. The following trick now helps:
- When importing the csv, do not open it directly with a mouse click, but in the "Data" tab via the menu item "From text" (see screenshot).
- Now select the corresponding csv file.
- Now the Excel text conversion wizard opens
- In step 1, select the "Separate" option.
- In the next step you define the separator - usually this is a semicolon (";") for csv files.
- Step 3 is now the decisive one: Here you can mark individual columns in the source csv and define the import format. To do this, mark the corresponding column in the conversion wizard (see screenshot point 1)
- To avoid the problem that Excel makes "5.5" May 5 or 3.6 June 3, press the "More" button (point 2 in the screenshot) and now enter a point in "Decimal separator" . " instead of ", " a (see screenshot number 3)
- This setting now ensures that the numbers are imported as such and not in the date format. Finish the import wizard with "Finish" - the numbers from your source csv are then numbers in the Excel table and not calendar data.
The examples and screenshots above relate to Excel 2010.