"MMMM yy"-date in google spreadsheet

google-apps-script google-sheets
Clox picture Clox · Nov 21, 2011 · Viewed 42.9k times · Source

I have a google spreadsheet in which I want a date with only the name of the month and the year, like September 2011, and I also want the month and year to be easily changeable. Is there any way of getting custom date formats to do this?

I figured out I could do like this: 

=TEXT(40295; "MMMM yy")

But then the datepicker can't be used anymore and changing the date is made impossibly hard.. Is there any good way of solving this?

Answer

Henrique G. Abreu picture Henrique G. Abreu · Dec 1, 2011

You can set a custom format to a cell using Google Apps Script.
Open the script editor (menu Tools > Script editor), paste this, save and Run > onOpen.

function onOpen() {
  SpreadsheetApp.getActive().addMenu(
    'Format', [{name:'Custom', functionName:'customFormat'}]);
}

function customFormat() {
  var format = Browser.inputBox('Write the format to be applied on the seleted cells');
  if( format !== 'cancel' )
    SpreadsheetApp.getActiveRange().setNumberFormat(format);
}

On your spreadsheet a new menu should appear in the end where you can pick the Custom entry to enter your custom format for the selected cells.

Related questions

How to get the correct range to set the value to a cell?

I want to set text or number in Google Sheet from script. I want to set Hello or number 9 in cell F2. I found this code so far: SpreadsheetApp.getActiveRange().setValue('hello'); but that doesn't specify which cell.

Read More
How do you add UI inside cells in a google spreadsheet using app script?

I'd like to add buttons to specific cells in Google docs spreadsheet. The apps script UI documentation talks about how to add a new panel, but it's not clear how UI in that panel could be attached to specific rows …

Read More
Sheet.getRange(1,1,1,12) what does the numbers in bracket specify?

Sheet.getRange(1,1,1,12) I cannot understand the arguments 1,1,1,12 . What is this - the sheet id or row or what? method getRange(row, column, optNumRows, optNumColumns) here what does optNumRows and optNumColumns mean???

Read More