从日期中提取月份和年份，并显示为MMM-YY [英] Extract month and year from Date and display as MMM-YY

问题描述

我是Google Apps脚本的新手，我很难进行日期格式转换

I am new to google apps script, I am having a hard time with date format conversion

我的源列中的数据格式为"mm/dd/yyyy".我想将其更改为"MMM-YY"，即我只需要提取月份和年份.以下是我失败的尝试

my source column has data in the format "mm/dd/yyyy". I would like to change this to "MMM-YY" i.e. I just need to extract Month and year. Below is my unsuccessful attempt

 // Get all values in column A on sheet titled "Transactions"
var ss = SpreadsheetApp.getActiveSpreadsheet();

var sourcesheet = ss.getSheetByName("Transactions");
var targetsheet = ss.getSheetByName("stage");  

  // get source range
  var source = sourcesheet.getRange("A:J");
  //get the data and place in array
  var alldata = sourcesheet.getRange("A:I").getValues();
  Logger.log(alldata.length)  ;

  //********************************************************************************//
  // loop through date column and change format
 for(var i=1; i<alldata.length; i++)    
  {

    alldata[i][0]= new Date(alldata[i][0]);

    var Mnth = alldata[i][0].getMonth() ;
    var Year = alldata[i][0].getYear() ;
    var Day = alldata[i][0].getDay() ;

    var Day2 = new Date(Year,Mnth,Day);

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


  }


  //********************************************************************************//
  // get destination range  
  var destination = targetsheet.getRange(2, 1, alldata.length, 11);

  // clear contents of destination sheet
  destination.clear();
  // copy values to destination range
  destination.setValues(alldata);

}

示例

源列的值="01/06/2019"写入输出列"1/19/2019"的值，但显示为"Jan-19"

Source column value = "01/06/2019" value written to output column "1/19/2019" but it displays as "Jan-19"

推荐答案

替换

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

作者

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

说明

1. 使用撇号防止Google表格将 MMM-YY 形式的值解释为 MMM-dd
2. 用 yy
替换 YY
3. 通过 ss.getSpreadsheetTimeZone()
替换 Session.getScriptTimeZone()

注意

1. YY 表示弱年，可能会导致一年中某些日期出现一些错误.为防止这种情况，请改用 yy .
2. Google表格和Google Apps脚本中用于日期格式的某些字母可能具有不同的含义.请参阅参考文献以正确使用它们.
3. 脚本时区和电子表格时区可能不同.由于您会将值传递给电子表格，因此最好使用其时区，而不要使用脚本中的时区.

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.

---

将日期显示为 MMM-yy 作为日期值而不是文本(字符串)值的解决方案，不需要使用Google Apps脚本，而是使用Google表格数字格式功能(点击格式>数字>更多格式>更多日期和时间格式)，那么您可以使用Google Apps脚本通过使用

---

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)

参考

日期和数字格式

在Google表格中

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

在 Utilities.formatDate(日期，时区，格式)

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

这篇关于从日期中提取月份和年份，并显示为MMM-YY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！