Google Sheets – Custom Number Format for Large Dollar Amounts

With the following piece of code you can style whatever number you want.

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom Format')
    .addItem('Apply format', 'myFormat').addToUi();
}

function myFormat() {
  var ss = SpreadsheetApp.getActive(), oCol = [];
  var aRange = ss.getActiveRange(), rValues = aRange.getValues();
  for(var i=0, iLen=rValues.length; i<iLen; i++) {
    var oRow = [];  // (Thanks Martin Hawksey)
    for(var j=0, jLen=rValues[0].length; j<jLen; j++) {
      if(typeof(rValues[i][j]) == 'number') {
        oRow.push(getFormat(rValues[i][j]));
      } else {
        oRow.push("");
      }
    }
  oCol.push(oRow);
  }
  aRange.setNumberFormats(oCol);
}

function getFormat(cell) {
  var g = "", s = "", c = "";  
  if(cell >= 0 && cell < 100) {
    c = '00"c"';
  } else if(cell >= 100 && cell < 10000) {
    s = '00"s "', c = '00"c"';
  } else if (cell >= 10000) {
    g = '00"g "', s = '00"s "', c = '00"c"';
  } else { 
    throw 'invalid entry';
  }
  return "#" + g + s + c;
}

Explained

The onOpen() will create a new menu entry. The myFormat will accept the selected reange and spit out the desired format.

Add the script under Tools>Script editor. Press the bug button to authenticate the script and you're on the go !!

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.