javascript - How to merge date and time as a datetime in Google Apps Spreadsheet script? - Stack Overflow

admin管理员组

文章数量:1312855

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?

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

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)

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.

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?

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

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)

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.

• javascript
• datetime
• google-apps-script
• date-manipulation

Share Improve this question Follow edited Jun 29, 2015 at 16:07 Deduplicator 45.7k77 gold badges7272 silver badges123123 bronze badges asked Oct 29, 2010 at 11:11 understackunderstack 11.6k2424 gold badges7979 silver badges100100 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

Ive got this answered in this Google Apps forum thread.

"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.

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) ); "

本文标签： javascriptHow to merge date and time as a datetime in Google Apps Spreadsheet scriptStack Overflow