如何从单元格中获取5位数字序列日期格式的字符串值到脚本中?

问题描述:

我在一个单元格中有一个公式,该公式通过合并电子表格中两个单元格的值来对另一个工作表进行vlookup.这是名称表,位于左侧,日期位于顶部.我将名称和日期值组合成一个字符串值,并将其作为vlookup键值使用,效果很好.现在,我基本上需要在Google脚本中执行相同的操作,但是日期值让我无所适从.在vlookup单元格公式中,日期值是5位数字的日期序列号.但是,当我在脚本中合并两个单元格值时,无法从单元格中获得相同的5位串行代码.我已经尝试了数字,字符串,文本等的所有转换.我可以使用什么函数在脚本中将这5位日期值作为字符串获取?

I have a formula in a cell that is doing a vlookup to another sheet by combining the values of two cells on my spreadsheet. It's a table of names on the left and dates up top. I'm combining the name and date values into a single string value and using that as by vlookup key value, which works great. Now I need to basically do the same thing in my google script, but the date value has me at a loss. In the vlookup cell formula, the date value is the 5 digit date serial code. However when I combine the two cell values in my script, I cannot get the same 5 digit serial code out of the cell. I have tried all conversions of number, string, text, ect. What function can I use to get this 5 digit date value as a string in my script?

function serialDatevalue(){
  var WB = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var NewDate = new Date();
  var NewDate =  WB.getRange('B1').getValue();
  var JSdate =  WB.getRange('B1').getValue();
  Browser.msgBox(Number(NewDate));

// Some others that I have tried
//  var JSdate = Date.parse(DateValue);
//  var JSdate = Number(DateValue);
//  var JSdate = Utilities.formatDate(DateValue, 'PST', '%');
}

例如,2019年4月13日=43568.我想将此5位数字值作为字符串.

for example, April 13, 2019 = 43568. I want to get this 5 digit value as a string.

我不断收到'Sat Apr 13 2019 00:00:00 GMT-0700(PDT)'或其他更长的整数值.

I keep getting 'Sat Apr 13 2019 00:00:00 GMT-0700 (PDT)', or other longer integer values.

请帮助.

Tanaike的答案 var serialNumber = (dateObject.getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953

来自 https://developers.google.com/sheets/api/指南/概念#datetime_serial_numbers

日期/时间序列号

Google表格与大多数其他电子表格一样 应用程序,将日期/时间值视为十进制值.这让你 在公式中对它们执行算术运算,因此您可以增加天数或 周,加上或减去两个日期/时间,然后执行其他类似的操作 操作.

Google Sheets, like most other spreadsheet applications, treats date/time values as decimal values. This lets you perform arithmetic on them in formulas, so you can increment days or weeks, add or subtract two date/times, and perform other similar operations.

Google表格使用

Google Sheets uses a form of epoch date that is commonly used in spreadsheets. The whole number portion of the value (left of the decimal) counts the days since December 30th 1899. The fractional portion (right of the decimal) counts the time as a fraction of one day. For example, January 1st 1900 at noon would be 2.5, 2 because it's two days after December 30th, 1899, and .5 because noon is half a day. February 1st 1900 at 3pm would be 33.625.

请注意,Google表格正确地将1900年视为普通年份 年,而不是a年.

Note that Google Sheets correctly treats the year 1900 as a common year, not a leap year.

另一方面,JavaScript使用1970年1月1日00:00 UTC作为时代日期,并以毫秒为单位.

By the other hand, JavaScript use January 1, 1970 00:00 UTC as the epoch date and use milliseconds as the unit.

请注意:用于将序列号从一个转换为另一个的自定义代码应考虑电子表格和Google Apps脚本是否使用相同的时区.

A word of caution: Custom code to make serialized number conversions from one to the other should consider if the spreadsheet and the Google Apps Script are using the same timezone or not.

相关

  • How to read value of fetched cell data as date google sheets API
  • Getting the number equalent of duration using GAS