How To Stop Excel From Changing Date Format

Excel keeps changing my numbers into dates.

Sometimes importing data into spreadsheets can requite you headaches. While this upshot is not direct related to Datawrapper, information technology's a common and known outcome with Excel – which can exist frustrating and time-consuming: Excel is trained to "detect" formats, and sometimes the software wrongly assumes that a sure number represents a date. If you enter 12/2, it will change to ii-December or 12. Feb. If you then enquire Excel to change the format to "Text", information technology shows you a weird high number like 44239.

Then how can you show a cavalcade with values like 12/2 without having it converted into dates?

How to get around this in Excel

The fox is to choose the format of the column before you enter or paste the data:

• Open up Excel
• Select the columns or cells you want to paste into, and choose "Text" in the format dropdown in the Abode tab. You can also press Cmd+1 to, select "Text" in that location and and then click "Ok".
• Now make certain that your data is pasted as text. To practice so, become to the Home tab, so choose the niggling arrow next to "Paste" then cull "Match Destination Formatting". (To accomplish the same result, you can besides go to Edit > Paste Special..., and then select "Text" earlier clicking "Ok".)
• You lot'll come across little green triangles in the top-left of your pasted cells. Select them, click on the warning sign ⚠️ and select "Ignore Error".

Hither'due south a 30sec video showing the whole procedure:

Please annotation that you tin can't perform calculations such every bit SUM with the pasted text.

Choosing the correct format in Datawrapper

Fifty-fifty if Excel now thinks that your numbers is text, our data visualization tool Datawrapper will be able to deal with them. As soon equally you upload the numbers to Datawrapper, it guesses what kind of data format your columns have.

If that judge is wrong, you can easily change the format again. Click on the cavalcade, and so choose the correct format in the drop-down carte du jour which pops upwardly on the left side:

Special formats to avert that Excel changes the number into a date

Excel documentation has some boosted tips on how to avoid that automatic change of numbers:

• Manual inspection
  If your dataset has only a few lines you tin manually check whether datapoints where wrongly inverse into dates. They are quite easy to spot in the columns.
• To and so change the format back you lot enter:
  • A infinite before the number. Press enter to make sure that this space remains in the cell. This is less recommend than the next option:
  • Add an apostrophe (') Earlier you lot enter the number, eastward.g. 'i/2. The apostrophe will not be displayed after pressing Enter.
  • Add together a null and space earlier number formats such as 1/ii, to avoid the alter to date.
  • Change numbers that could be misread from 1/2 to 0.5 (or 0,5, if that is your local number format).

Last updated on Dec 6, 2021

Source: https://academy.datawrapper.de/article/89-prevent-excel-from-changing-numbers-into-dates

Posted by: bookercantences88.blogspot.com

Share this post