如何在Google Apps电子表格脚本中将日期和时间合并为日期时间？ [英] How to merge date and time as a datetime in Google Apps Spreadsheet script?

问题描述

我在Google App电子表格中以dd / mm / yyyy格式显示日期列，HH：MM AM / PM格式的时间列为HH。在应用脚本中，我想将这两个值作为日期时间加入。如何做到这一点？

I've a Date column in 'dd/mm/yyyy' format and Time column in 'HH:MM AM/PM' format in Google App spreadsheet. In app script, I want to join these 2 values as a datetime. How can I do this?

示例日期：13/12/2010，时间：4:30 PM
然后我想得到一个综合值'13 / 12/2010 4:30 PM'作为datetime对象。

Example- date : 13/12/2010, time : 4:30 PM Then I want to get a combined value like '13/12/2010 4:30 PM' as a datetime object.

我试图使用getYear，getDay JS函数分别解析日期和时间。并使用

I tried to parse date and time separately using getYear, getDay JS functions. And created a new date using

new Date(year, month, day, hours, minutes)

但由于某种原因，4:30被显示为一个完整的奇怪的日期，如1899年11月30日上午10:30。我可以准确地提取年，日，月，而不是小时和分钟。

But for some reason time '4:30' is being shown as a full weird date like 30 Nov 1899 10:30AM. I can accurately extract year, day and month but not hours and minutes.

推荐答案

我在此 Google Apps论坛主题。

Ive got this answered in this Google Apps forum thread.

首先，日期和时间之间没有区别（格式除外），它们都是Date对象现在出现问题..
数值是电子表格中的日期/时间是自纪元以来的天数，所以时间实际上是数字的小数部分，如果在电子表格中键入0（零），然后选择日期时间格式，您将看到电子表格当前时区的时代是12/30/1899 0:00:00，即在不同的电子表格中配置不同的时区，时代可能会改变，如果您使用相同的数字值两个电子表格（例如使用ImportRange），你会有所不同对我很奇怪这并不总是这样，但是谷歌似乎认为这对用户来说更好，因为如果更改电子表格时区设置，日期将不会在视觉上变化，例如如果您在电子表格中输入1/1/10 14:00，然后将时区更改为其他日期，则日期/时间不会更改。在新时代，仍然是14点。

"First, there's no difference between date and time (besides the formatting), they are all Date objects. Now to the problem.. The number which is the value of the date/time in the spreadsheet, is the number of days since the "epoch", so the "time" is actually the decimal part of the number. If you type 0 (zero) in your spreadsheet and then select the datetime format, you'll see that the epoch is "12/30/1899 0:00:00" in the spreadsheet current timezone. i.e. in different spreadsheets, configured with different timezones, the epoch may change. And if you operate the same number value in two spreadsheet (e.g. using ImportRange) you'll the different. A behavior that is very odd to me. It was not always like this, but google seemed to think this is better to the user, because if one changes the spreadsheet timezone settings, the dates will not change visually, e.g. if you type "1/1/10 14:00" in the spreadsheet and then change the timezone to any other, the date/time will not change. It will still be 14:00 in the new timezone.

现在，在GAS的时代是1/1/1970 0:00:00 GMT + 0现在要关注时区。所以，时代是不同的，也是最可能的时区。这使得GAS和电子表格之间的运行日期完全混乱。
要测试GAS纪元，请执行一个range.setValue（new Date（0））;

Now, in GAS the epoch is "1/1/1970 0:00:00 GMT+0", now matter the timezone. So, the epoch is different and most likely the timezone too. This makes operating dates between GAS and the spreadsheet a total mess. To test the GAS epoch, do a range.setValue( new Date(0) ); "

这篇关于如何在Google Apps电子表格脚本中将日期和时间合并为日期时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！