问题描述
我已经在这个圈子里走了一圈...我有一个包含两个日期的电子表格，我需要找到两者之间经过的年数（即某人在给定日期的年龄） ;这是替代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对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！