Paste number in Google Sheets recognizing the proper format

user_7032878 0
Viewed 42

I've changed my locale in Google Sheets (Settings > Locale) to use the Italian format, with the comma as decimal separator, and dot as thousands separator (thus 1.173,00 means one-thousand-one-hundred-seventy-three). I'm trying to figure out how to paste correctly in a cell a number which comes from the origin as "1.173" and Google Sheets is interpreting that as 3.53.00 Why? Everything works fine if I include the decimals (pasting "1.173,00") but the source does not includes them, therefore I'm stuck with this problem.

Thank you

google-sheets
1 Answers

Google Sheets is automatically interpreting 1.173 as a duration rather than a number. It reads it as 1 hour and 173 minutes, and automatically corrects it to 3 hours and 53 minutes instead.

Based on my own experimentation, it doesn't seem like there's a fix for this: regardless of what number format is applied to a cell or range before inputting 1.173, Sheets still treats it as a duration. Formatting as plain text allows the cell to display 1.173, but trying to convert that text to a number for use in any sort of calculation (through the use of =VALUE, for example) seems to have issues.

user_193619360
Related