1

I have a number cell formatted in this way:

#,#" days";.

Nevertheless, if I write 1.5the output is 1 days even if the content is still 1.5.

Otherwise, if I write 1,5 the output will be as it is 1,5—without the text days.

As you can see from the screenshot below, according to the formula output suggestion it should show 1,5 days.

number_format

Do you have any idea?

NEW DETAILS:

@Jacob Jan Tuinstra: Thanks, I've tried your solution of the increase decimal places button that adds the decimal also when the decimal is 0. I would like to avoid it. That's the target I would reach, and I don't have an answer for it.

What happens doing the increase decimal places is the following formula:

enter image description here

In the column, when I have days without decimal like 2 days it will show 2.0 days. I would like to avoid the manual correction with the “decrease decimal places” because I have to manage a huge amount of data.

  • Pnuts, I've just figured out that I wrote wrongly the example. By the way the Locale is on United Kingdom. – GianlucaR Sep 29 '14 at 14:16
  • You seem to know a lot about the topic. Why don't you 1) answer your own question or 2) edit your post to have the explicit request/result you're looking for. – Jacob Jan Tuinstra Sep 29 '14 at 15:39
  • Thanks for the suggestion. I've edited the post to make what I want more explicit. I hope is more clear now. – GianlucaR Sep 29 '14 at 15:50
2

I'm afraid this is more answering "do you have any idea?" rather than giving a solution.

The primary issue here is your locale, as mentioned in your comment on the question. Custom number formats are not agnostic to the locale-specific number formats. In the case of the UK locale, commas are regarded as thousands separators, and periods are regarded as decimal separators. So your custom format #,#" days" is essentially just instructing Sheets to format with a comma as a thousands separator, with no decimal places (it is functionally equivalent to #,###" days".

As you saw, when you directly enter 1,5, this isn't formatted at all, because in the UK locale this is not recognised as a valid number (or date, or anything numeric), and is stored as a text string.

So an option is to change to a locale that uses a comma as a decimal separator (eg Italy). However (and this is a big "however") I have had a lot of trouble getting the custom formats to work in such locales; the implementation appears to leave a bit to be desired.

Another option is to keep your current locale, and make do with a period for a decimal separator, and use the format #.# "days". The "however" here is that the decimal point is included even when it is not needed (ie whole numbers), which is irritating, and I don't know of a workaround in Sheets using custom formatting alone.

A third option is to use spreadsheet formulae to reproduce the values in the appropriate format. The reproduced data would be text strings, so you should still reference the raw values (which might be in a column that could be hidden) in formulae. Downside is of course the duplication of data and reduced usability.

And lastly, Google Apps Script could auto convert values to text strings in the desired format; again you would have to consider formulae that reference these cells.

I hope this gives some insight, and I really hope to be able to delete this answer if someone has a watertight solution that can be achieved in custom formatting alone.

  • 1
    Thanks Adam. For my purpose the #.#" days" has been fine with the Local on UK. I won't care about the dot even if is a bit irritating, it's true. Thanks – GianlucaR Sep 30 '14 at 9:30
  • 1
    UK (and Australian) locale, with format "#,#" days", 1.5 is formatted as 2 days (indeed, not 1 days, which may be what you were getting at). And yes, I think I discovered the issue (frankly, a bug) underlying my comment "I have had a lot of trouble getting the custom formats to work in such (comma decimal) locales" - not sure if I should edit it into the answer though because may be drifting off topic. – AdamL Oct 1 '14 at 2:29
0

You need to add more decimals.

Screenshot

enter image description here

  • Hi Jacob, thanks for your suggestion. But this solution that fixes the issue, shows also insignificant 0 in the result. Basically because it changes the custome number format to #.00" days";. Instead I would like this function: #.##" days"; that following these Google sentences means: - 0 italic_A digit in the number. An insignificant 0 will appear in the results._italic - # italic_A digit in the number. An insignificant 0 will not appear in the results._italic As you can read here: link – GianlucaR Sep 29 '14 at 15:33
  • @GianlucaR Use the custom format you had and press the button (red circle). This will increase the number of decimals by one: 2 turns into 1.5. – Jacob Jan Tuinstra Sep 29 '14 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.