javascript - How do I use a negative timestamp to do math? -

i'm using google spreadsheets , have sheet in following format:

start | end     | partial result | expected | total 08:00 | 12:00   | 04:00          | 05:00    | -01:00 14:00 | 18:00   | 04:00          | 05:00    | -01:00 ----------------------------------------------------                                  | month tot| -02:00 

as can see, allows negative duration stamps. causing me lot of trouble in google scripts.

i want email monthly summary.

here's simple snippet:

var sheet = spreadsheetapp.getactive().getsheetbyname(...); var monthlytotal = sheet.getrange(1, 1).getvalue(); //the total @ cell a1 

now, when debug, shows monthlytotal date object, set tue dec 26 1899 14:50:28 gmt-0300 (brt) (this quite correct, result -81:16:00, subtracted 81:16 1970-01-01 00:00:00, no issues there).

now, have no idea how duration format (-81:16). have tried converting date object negative timestamp, +monthlytotal returns believe wrong value (-2212132172000).

i have no idea how turn date object before 1970 timestamp and, consequence, don't know how proceed.

this question has been answered on webapps.stackexchange.com.

short answer

don't use .getvalue() on date/time cells. instead, put =to_text(a1) somewhere (or have a1 containing to_text(sum(e2:e)), , use .getvalue() string. numbers , strings pass between sheets , scripts reliably, unlike dates , times.

explanation

unlike unix operating systems epoch begins @ 1970-01-01, google sheets use december 30, 1899 0:00:00 0 time, can see entering =to_date(0) anywhere in spreadsheet.

in principle, apply .valueof() duration value returned .getvalue(): returns javascript timestamp, number of milliseconds beginning of 1970. dividing 1000 gives unix timestamp.

however, problematic because unix timestamp , consequently javascript timestamp measured moment 1970-01-01 00:00:00 in utc, while 0 mark of google sheets in local timezone.

if try adjust timezones using new date().gettimezoneoffset(); warned script time zone may different sheet time zone: documentation says "a frequent source of scripting bugs". method spreadsheet.getspreadsheettimezone() gives timezone of spreadsheet itself, it's returned string 'america/new york', does not directly translate offset.