将Google电子表格日期转换为JS Date对象？ [英] Converting Google spreadsheet date into a JS Date object?

问题描述

我已经在这个圈子里走了一圈...我有一个包含两个日期的电子表格，我需要找到两者之间经过的年数（即某人在给定日期的年龄） ;这是替代Excel的DATEDIF）。

I've been going round in circles on this one... I've got a spreadsheet which holds two dates, and I need to find the number of elapsed years between the two (ie. someone's age at a given date; this is a replacement for Excel's DATEDIF).

第一步是将Google的序列号转换为JS Date对象，但似乎没有Date构造函数这样做任何想法？

The first step is to convert Google's serial number into a JS Date object, but there doesn't appear to be Date constructor that does this. Any ideas?

谢谢。

推荐答案

我知道你很开心以您的解决方案为依据，但我只想添加我对Google Apps脚本处理日期的观察结果，这些日期是通过自定义函数传递的，或者通过getValue（）从单元格中检索。

I know you are happy with your solution as it stands, but I just wanted to add my observations of how Google Apps Script deals with "dates", either passed in a custom function, or retrieved from a cell with getValue().

我的经验法则是，如果表格（电子表格应用程序）提供格式为的值作为日期（通过自动强制或用户设置格式），那么Google Apps脚本将自动将此值作为日期对象。

My rule of thumb is that if Sheets (the spreadsheet application) is providing a value formatted as a date (either by automatic coercion, or the user setting the format), then Google Apps Script will automatically hold this value as a date object.

例如：

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

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

If you enter 1/1/13 in A1, and in another cell you invoke =returnDate(A1), it will return the same date (as it would if you simply had return value; in the code). However, watch what happens when you format A1 as "Normal" (convert it to a numerical value). Here, the "Sheets serial number" (number of days from 30/12/1899) is converted into a date object by Google Apps Script, but in GAS it is "regarded" as the number of milliseconds from midnight 1/1/1970. So you might get unexpected results if you are passing numerical values that you believe are representative of a date.

还可以比较：

= 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（）从有效的字符串成功创建日期对象，但请注意，表格自动强制到当前世界，而GAS强制两位数的年份到1900年代。

The latter two "work", because new Date() successfully creates the date object from a valid string, but note that Sheets automatically coerces to the current century, while GAS coerces a two-digit year to the 1900's.

所以IMO如果你希望它的行为与Excel一样（也就是说，数值作为日期的序列号），您需要首先测试传递的参数是否为日期对象（或有效文本字符串），如果不是，则将其从从30/12 / 18 99到1/1970的毫秒，然后 new Date（）它。

So IMO if you wanted it to behave exactly as it would in Excel (that is, "regard" a numerical value as a serial number for a date), you would need to first test if the passed parameter is a date object (or "valid" text string), and if not, mathematically convert it from "days from 30/12/1899" to "milliseconds from 1/1/1970", and then new Date() it.

道歉对于长期的帖子。

这篇关于将Google电子表格日期转换为JS Date对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！