Extract Month And Year From Date And Display As Mmm-yy

I am new to google apps script, I am having a hard time with date format conversion my source column has data in the format 'mm/dd/yyyy'. I would like to change this to 'MMM-YY' i

Solution 1:

Replace

alldata[i][10] = Utilities.formatDate(Day2, Session.getScriptTimeZone(), "MMM-YY");

by

alldata[i][10] = "'" + Utilities.formatDate(Day2, ss.getSpreadsheetTimeZone(), "MMM-yy");

Explanation

1. Prepend an apostrophe to prevent that Google Sheets from interpreting the values of the form MMM-YY as MMM-dd
2. Replace YY by yy
3. Replace Session.getScriptTimeZone() by ss.getSpreadsheetTimeZone()

NOTES

1. YY means weak year that could lead to some errors in certain dates of the year. To prevent this, instead use yy.
2. Some letters used for date format in Google Sheets and in Google Apps Script could mean different things. See the references to use them properly.
3. The script time zone and the spreadsheet time zone could be different. Since you will passing the values to the spreadsheet, it's better to use its time zone instead of the one from the script.

---

The solution to get the dates displayed as MMM-yy as date values instead of text (string) values, doesn't require to use Google Apps Script, instead use the Google Sheets number format feature (click on Format > Number > More Formats > More date and time formats), well you could use Google Apps Script to set the number format by using setNumberFormat(numberFormat)

References

Date and Number Formats

In Google Sheets

• https://developers.google.com/sheets/api/guides/formats

In Utilities.formatDate(date,timezone,format)

• https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Solution 2:

This works with the format as MMM-yy

functionmyFunction1() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var ssh=ss.getSheetByName("Transactions");
  var tsh=ss.getSheetByName("stage");  
  var srg=ssh.getRange(1,1,ssh.getLastRow(),11);
  var data=srg.getValues();
  data.forEach(function(r,i){r[10]=Utilities.formatDate(newDate(data[i][0]),Session.getScriptTimeZone(), "MMM-yy");});
  var destination=tsh.getRange(1,1,data.length,11);
  destination.clear();
  destination.setValues(data);
}

reference

Solution 3:

You just copy date values to the target sheet without any changes whatsoever and the apply custom number format to the target range.

var datesRange = sheet.getRange("A1:A");
var values = datesRange.getValues();
var destination = targetSheet.getRange("A1:A");
destination.setValues(values).setNumberFormat("mmm-yy");

As Ruben pointed out, you can simply change the number format in UI (no need for scripting) so his solution is probably the best one.