When importing data into Excel from external programs, sometimes a very unpleasant problem arises – fractional numbers turn into dates:
This usually happens if the regional settings of the external program do not match the regional settings of Windows and Excel. For example, you are loading data from an American site or a European accounting system (where there is a dot between the integer and fractional parts), and in Excel you have settings (where there is a comma between the integer and fractional parts, and the dot is used as a separator in the date).
When importing, Excel, as expected, tries to recognize the type of input data and follows a simple logic – if something contains a dot (i.e. date separator) and looks like a date – it will be converted to a date. Everything that does not look like a date will remain text.
Let’s look at all possible scenarios using the example of corrupted data in the picture above:
- In cell A1, the original number 153.4182 remained text, because does not look like a date at all (there is no 153rd month)
- In cell A2, the number 5.1067 also remained the text, tk. Excel can’t have the date May 1067 – the earliest date that Excel can work with is January 1, 1900.
- But in cell A3, there was originally a number 5.1987, which looks exactly like a date, so Excel turned it into 1 May 1987, helpfully adding one as the day:
- The same thing happened in cell A4, where the number was originally 12.6923. Moreover, the year 6923 did not bother Excel at all – for him, the main thing is that the date should not be earlier than 1900. So welcome to the distant future:
- The only thing that is recognized correctly is numbers without a fractional part (number 1000 in cell A6). And thanks for that 🙂
Here are the options. And if text numbers can still be cured by a banal replacement of a dot with a comma, then such a number will no longer work with numbers turned into dates. And an attempt to change their format to a numeric one will no longer display the original values, but the internal Excel date codes – the number of days from 01.01.1900/XNUMX/XNUMX to the current date:
This whole story is treated in three fundamentally different ways.
Method 1. In advance in the settings
If the data has not yet been loaded, then you can pre-set the point as a separator of the integer and fractional parts through File – Options – Advanced (File — Options — Advanced):
Remove the checkbox Use system separators (Use system separators) and enter a dot in the field Integer and decimal separator (Decimal separator).
After that, you can safely import the data – there will be no problems.
Method 2. Formula
If the data is already loaded, then a simple formula can be used to get the original numbers from the corrupted data-text-number porridge:
=—IF(CELL(“format”,A1)=”G”; SUBSTITUTE(A1;”.”;”,”); TEXT(A1;”M,YYYY”))
In the English version it will be:
=—IF(CELL(«format«;A1)=»G»; SUBSTITUTE(A1;».»;»,»); TEXT(A1;»M,YYYY«))
The logic here is simple:
- CELL function (CELL) determines the numeric format of the source cell and returns “G” for text/numbers or “D3” for dates as the result.
- If there is text in the source cell, then we replace the dot with a comma using the SUBSTITUTE function (SUBSTITUTE).
- If there is a date in the source cell, then we display it in the format “month number – comma – year number” using the TEXT function (TEXT).
- To convert the resulting text value into a full-fledged number – we perform a meaningless mathematical operation – we add two minus signs in front of the formula, simulating double multiplication by -1.
Method 3. Macro
If such a procedure for treating corrupted numbers has to be performed often, then it makes sense to automate the process with a macro. To do this, press the keyboard shortcut Alt+F11 or button Visual Basic tab developer (Developer), insert a new empty module into our book through the menu Insert – Module and copy the following code there:
Sub Fix_Numbers_From_Dates() Dim num As Double, cell As Range For Each cell In Selection If Not IsEmpty(cell) Then If cell.NumberFormat = "General" Then num = CDbl(Replace(cell, ".", ",")) Else num = CDbl(Format(cell, "m,yyyy")) End If cell.Clear cell.Value = num End If Next cell End Sub
It remains to select problematic cells and run the created macro with a keyboard shortcut Alt+F8 or via command Macros tab developer (Developer — Macros). All corrupted numbers will be corrected immediately.
- How Excel actually works with dates and times
- Replacing text with SUBSTITUTE
- VLOOKUP function and numbers-as-text