Excel number type changes 1.25 to 43125

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.

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.