"MMMM yy"-date in google spreadsheet

Question

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 1

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.

Answer 2

Google Spreadsheet does not yet permit you to apply a custom number format to a cell.

You can of course enter the date into a cell, and then reference that date in a second cell:

A1:4/27/2010, A2=TEXT(A1;"MMMM yy") 

This would meet your requirements: it would display the date the way you wanted, and allow the date to be easily changeable.

But it has the undesirable side effect of having the date appearing twice on the sheet. I often work around side effects like this by printing or exporting a range instead of the entire sheet. So maybe there is also a practical workaround in your case.