将Google电子表格日期转换为JS日期对象

Question

我一直在绕圈子…我有一个电子表格，其中持有两个日期，我需要找到经过的年份之间的两个(即。年龄:某人在某一特定日期的年龄;这是Excel的DATEDIF)的替代品。

第一步是将Google的序列号转换为JS Date对象，但似乎没有Date构造函数可以完成此操作。什么好主意吗?

谢谢。

Answer 1

将Google电子表格日期转换为javascript日期:

var JSdate = Date.parse(Cell.getValue())

将javascript日期转换为Google电子表格日期:

function GoogleDate( JSdate ) { 
   var D = new Date(JSdate) ;
   var Null = new Date(Date.UTC(1899,11,30,0,0,0,0)) ; // the starting value for Google
   return ((D.getTime()  - Null.getTime())/60000 - D.getTimezoneOffset()) / 1440 ;
}

Answer 2

我知道你对你的解决方案很满意，但我只是想添加我对Google Apps Script如何处理"日期"的观察，无论是在自定义函数中传递，还是从带有getValue()的单元格中检索。

我的经验法则是，如果Sheets(电子表格应用程序)提供了一个格式化为的值作为日期(通过自动强制转换或用户设置格式)，那么Google Apps Script将自动将该值保存为日期对象。

,

function returnDate(value) {
  return new Date(value);  
}

如果您在A1中输入1/1/13，并且在另一个单元格中调用=returnDate(A1)，它将返回相同的日期(就像您在代码中仅使用return value;一样)。但是，请注意将A1格式化为"Normal"(将其转换为数值)时会发生什么。这里，"工作表序列号"(从1899年12月30日开始的天数)被Google Apps Script转换为日期对象，但在GAS中，它被"视为"从1970年1月1日午夜开始的毫秒数。因此，如果您正在传递您认为代表日期的数值，则可能会得到意想不到的结果。

也比较:

=returnDate(DATE(2013;1;1))

=returnDate(VALUE("1/1/13"))

=returnDate(DATEVALUE("1/1/13"))

=returnDate("1/1/13")

=returnDate("1/1/2013")

后两个"有效"，因为new Date()成功地从一个有效字符串创建了日期对象，但请注意，Sheets会自动强制转换为当前的世纪，而GAS会强制转换为20世纪的两位数年份。

所以在我看来，如果你想让它的行为完全像在Excel中一样(也就是说，"考虑"一个数值作为日期的序列号)，你需要首先测试传递的参数是否是一个日期对象(或"有效"文本字符串)，如果不是，用数学方法将它从"days from 30/12/1899"转换为"milliseconds from 1/1/1970"，然后new Date()它。

为这篇冗长的文章道歉。

Answer 3

我是这样做的:

function numberToDate(number){
  var date = new Date(number * 24 * 60 * 60 * 1000);
  date.setFullYear(date.getFullYear() - 70);
  date.setDate(date.getDate() - 1);
  return (date);
}

这可能看起来有点脏，但这是我目前找到的唯一解决方案

Answer 4

在进行了更多的实验之后，结果证明它可以正常工作，这有点令人惊讶。new Date(cell)似乎在内部将序列号转换为一个足以创建日期对象的字符串。完整的答案:

function datedif(first, second, format) {
  var e1  = new Date(first);
  var e2  = new Date(second);
  var age = e2.getFullYear() - e1.getFullYear();
  if(
      (e2.getMonth() <  e1.getMonth()) || 
     ((e2.getMonth() == e1.getMonth()) && (e2.getDate() < e1.getDate())))
    age--;
  return age;
}

Answer 5

适合我

private static getDateFromGoogle(serialNumber: number): Date {
  // Google     30 Dec 1899
  // Javascript 01 Jan 1970
  return new Date((serialNumber - 25569) * 86400000);
}

Answer 6

你可以这样尝试:

return new Date(1900, 0, --excelDate)

Answer 7

首先，你必须从google表配置中获得时区设置。

那么，这就是完美的解决方案:

import { zonedTimeToUtc, utcToZonedTime } from "date-fns-tz";
const SheetDate = {
  origin: Date.UTC(1899, 11, 30, 0, 0, 0, 0),
  dayToMs: 24 * 60 * 60 * 1000
};
function serialToDate(d: number, sheetTimeZone: string): Date {
  return zonedTimeToUtc(
    new Date(d * SheetDate.dayToMs + SheetDate.origin),
    sheetTimeZone
  );
}
function dateToSerial(date: Date, sheetTimeZone: string) {
  const msec = utcToZonedTime(date, sheetTimeZone).getTime() - SheetDate.origin;
  return msec / SheetDate.dayToMs;
}

Answer 8

通过"serial number"我猜您正在谈论从epoch开始的以秒或毫秒为单位的unix时间。您可以简单地使用标准的Javascript Date对象:

new Date(value);

谷歌是你的朋友。这里有一些参考资料供您参考:

• W3 Schools参考
• W3 Schools教程
• MDN参考

Javascript允许您对两个日期进行简单的减法，以ms为单位返回时差。

var timeDiffInMS = date2 - date1;

这应该是所有你需要弄清楚的，所以我将把年份的计算留给读者作为练习。