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

Question

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

Answer 1

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