"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.

Answer 3

I thought yy just gives the 2 digit year.

I used the following:

=text(E2,"MMMM YYYY")

E2 was the specific cell I used, but you could use any cell.

Answer 4

You can enter any format (for dates or others) as a Custom Number Format.

Highlight the cell range and Go to Format > Number > More Formats > Custom Number Format. Then enter

mmmm" "yyyy

gives "September 2011"

or any other format

ddd" "mm"/"dd"/"yyyy

would give "Mon 09/11/2011"

note the missing quote at the beginning and the end. it shows how it will display as you experiment. Quotes in the beginning or end give you invalid format Saves you having two fields (the data, and the text() formatted one)

Its not intuitive (either the format, or where to put it). But works better than importing an xls.

Answer 5

I accidentally found a workaround for a custom date format. I had a custom date format using Excel. When uploading the Excel file, the date format (mm/dd/yyyy hh:mm am/pm) stayed in that format even though that was not a supported Google Sheet format. Then using the format painter, I was able to copy that format to other cells within Google Sheet. I know this is not an ideal solution, but seems to work. I have not played with how many other custom formats I could create in Excel, convert to Google Sheet and then use format painter to use with other cells.