52

When I open a plain new Excel sheet (2016), change the type of the cell to Number and enter the number 1.25, it will change the entered number to the value 43125,00. I get similar results when I enter 1.01 or 1.9, but not if I enter 1.99 – that is treated as text or at least something unparsable as a number.

I understand that I have to use , to correctly input numbers.

My question is, what happens when I enter 1.25 as a number? Why do I get 43125,00?

Scott - Слава Україні
  • 21,399
  • 46
  • 64
  • 121
Croo
  • 603
  • 5
  • 7
  • 1
    Note that "cell type" isn't the type of the data - it only affects how the data is displayed, not how the input is interpreted. All data in Excel is in variants. – Luaan Jun 02 '18 at 14:00

2 Answers2

108

For your locale settings, , is the decimal separator, so that's what you need to use to enter a number. If you use a period, the input will be interpreted as something other than a number; what that is depends on the content.

If what you enter could be a valid date, a period is being interpreted as a date separator. Excel thinks 1.25 is a date (January 25, 2018), which is represented internally as 43125 (the number of days since January 0, 1900).

Since you formatted the cell as a number rather than a date, you're seeing the numerical representation of the date. The default decimal places is set to two, giving you the zeros at the end. It's pure coincidence that the "125" pattern is repeated in the day count representing that date.

Both 1.01 and 1.9 could be dates, so the same thing happens to them. But 1.99 cannot be a date, so the literal value, as entered, is stored as text.

fixer1234
  • 27,064
  • 61
  • 75
  • 116
  • 5
    This is annoying in excel, I work around by adding a single speech mark before the number, so "1.25" becomes " '1.25" and fortunately excel treats it as a number. – Kevin Anthony Oppegaard Rose May 30 '18 at 07:50
  • 19
    @KevinAnthonyOppegaardRose: in this question decimal separator is `,`, if you want to enter numbers you need to use the decimal separator. If you use other characters Excel will interpret it another way. – Máté Juhász May 30 '18 at 07:54
  • 21
    @KevinAnthonyOppegaardRose The speech mark ' is the way to make Excel take anything as text, so it really is not treating it as a number but as text, and after a ' you can write anything and Excel will not try to interpret or convert it – fernando.reyes May 30 '18 at 15:38
  • 24
    and it's called the [apostrophe](https://en.wikipedia.org/wiki/Apostrophe) btw – phuclv May 30 '18 at 17:22
  • Look for a way to teach excel which formats should it recognise as dates. As far as I remember you can set that, just be aware of the consequences (you turn it off, then it won't work for all dates in general). – Ister May 31 '18 at 09:18
  • 1
    Your answer is correct but the described behaviour is completely stupid. Excel is so old, expensive and widely used that this simply shouldn't happen. Values should be parsed as columns, not independently as cells. If a potential thousand separator is used only once at most in every number and at different places, it's probably a decimal separator. `1.5` might be a date amid a column of decimals but it's probably a decimal too. `3,14` might be a string, it's probably a decimal, though. – Eric Duminil Jun 01 '18 at 14:02
  • 2
    @EricDuminil So you think it's stupid that Excel guesses at types, and you want to solve it by having Excel make even *more* guesses? What could possibly go wrong :) It's certainly weird that it doesn't preserve one type even in a table column, but the truth is - have you *seen* how people use Excel? In the end, "it behaves the same way in all contexts" is at least *consistent*. And the OP's problem is 100% about them not using their locale's number format - are you really trying to say Excel shouldn't expect people to use their local number (etc.) formats? – Luaan Jun 02 '18 at 13:58
  • @Luaan: No, I'd expect less guesswork. When in doubt : the cell should be a number. A string or a date should never been hidden somewhere between numbers. When only one separator is present, it should be a decimal separator. This logic would be much easier than now, it would be more reliable and more consistent. In Germany, the locale is `123.456,789` but many people work in an international team and write `123 456.789` or just `123456.789`. – Eric Duminil Jun 02 '18 at 14:16
  • 2
    @EricDuminil Right, so you've never seen anyone who isn't a programmer use Excel. All the assumptions you're making here are broken by a significant number of people who use Excel. Nobody prevents you from using the locale you want if you have people from different locales - why would you break things for other people when the feature you're asking for is already there? Excel never changes a number to anything else - you're just typing something that isn't a number and complaining it doesn't recognise it as a number. That's asking for *more* guesswork, not less. – Luaan Jun 02 '18 at 14:28
  • @Luaan: It looks like we're both making wrong assumptions, then. The problem isn't only when typing numbers. It would be relatively easy to stick to the current locale. The problem is also when opening csv files or copy-pasting data from another source. Not only programmers need those basic features. – Eric Duminil Jun 02 '18 at 15:09
3

After reading the comments, I realized now that the mechanism behind the problem is more complicated than I thought. First, the decimal separator is a comma and not a full stop. Second, Excel classifies the input value as a 'date' value but represents it using the 'number' format.

One solution to the posted problem is to change the decimal separator, and it can be done from Excel, Options -> Advanced -> Editing options -> Decimal separator. If this is fixed, Excel would not have classified the input value as a 'date' value.

The decimal separator is not something I would immediately consider because, who would use a comma as the decimal separator? And yet, interestingly and surprisingly, half the world actually uses it (https://en.wikipedia.org/wiki/Decimal_separator).

However, I did recognize the 43125 as a 'date' value in numerical form. If we take away the issue about the decimal separator, a similar example would be 1-25, which Excel would interpret as a 'date' value and under the 'number' format gives 45658.00. More confusingly is that Excel allows custom formats so that the 'date' value can appear as 1.25 or 1,25 or any other form.

I used a lot of 'date' values in spreadsheets for calculations and they can be annoying. Occasionally when I reopen these spreadsheets, Excel (or Openoffice) would think they know better and automatically convert these date columns from the 'date' format to the 'number' format for no reasons. In these situations, right-click mouse -> Format Cells -> Number and reset to the correct format for the cells/columns would recover the data.

wtong
  • 77
  • 2
  • No. This is an automated process in Excel. See the answer from fixer1234 – Kevin Anthony Oppegaard Rose May 30 '18 at 13:38
  • 1
    Excel thinks the input is a 'date' and automatically changes the number format to 'date'. What I supplied here is a way to 'tell' Excel the input is a 'number' and fix the format to the correct one. – wtong May 30 '18 at 14:29
  • 1
    I don’t agree that the first paragraph is missing what’s happening.  It does seem to be a complaint, along the lines of [Eric Duminil’s comment](https://superuser.com/q/1327085/150988#comment1981504_1327091).  Most of us will agree that, when you type something into a blank cell formatted as “General”, Excel should try its best to figure out what your input means.  But there’s a school of thought that, when you type something into a blank cell formatted as “Number”, Excel should try its best to interpret that input as a number.   … (Cont’d) – Scott - Слава Україні Jun 01 '18 at 20:09
  • (Cont’d) …  One might argue that changing the implementation now would break things.  On the other hand, the current implementation is not clearly consistent.  For example, in my English (United States) system, if I type ``10-2`` or ``10/2`` into a cell formatted as “General”, I get “2-Oct”.  Do the same thing with a cell formatted as “Number”, and the ``10-2`` gives me “43375.00” (numeric value of “2-Oct-2018”), but the ``10/2`` gives me “5.00”.  … (Cont’d) – Scott - Слава Україні Jun 01 '18 at 20:09
  • (Cont’d) …  You could also say that, if it’s going to interpret the input as a date, it should switch the display format to Date, so the user isn’t completely blindsided. Of course, that would probably ruffle some feathers, too. – Scott - Слава Україні Jun 01 '18 at 20:09
  • Yet another place where Microsoft tries to think for me, gets it wrong, and constantly bites me on the ass. I use a-b labelling for parts and subparts and you can see where this leads to half of it turning randomly into dates. – JKreft Jun 01 '18 at 21:44
  • @wtong, this contains some good stuff, but it's framed as an explanation of why the original version wasn't accurate. This is the answer now; the original version (and your thought process or learning process to get here) doesn't matter. Most good answers require some research, and the idea gets refined along the way; it's just the resulting answer that counts. Consider revising the answer to just focus on responding to what was asked in the question using what you now know. The content wouldn't be very different, just the focus on "what is" and "why" rather than "what you previously thought". – fixer1234 Jun 01 '18 at 21:53
  • 1
    @JKreft I think Excel does the whole thing only if you pick a specific category and then insert an invalid value. So Excel only bites you in the ass if you first make a mistake. Guessing what the user meant from invalid input is simply very difficult. – Voo Jun 02 '18 at 08:34
  • @voo No, that's nonsense. The default cell type is "General" and entering any "5-3" style number into it automatically converts the cell format to date. – JKreft Jun 02 '18 at 21:15
  • @wtong My point was that I want it to be "general", IE "text" by default, and not assume things about my input. There doesn't appear to be a way to fix this behavior in the options. If I enter "5-3" (meaning level 5, floor 3, for example), I don't want Excel to turn it into a date. – JKreft Jun 22 '18 at 11:22