title: Time zones in Google Apps Script
body: <p>I want to convert a date object into a "serial number" that is consistent with the underlying value of dates used in Google Spreadsheets. My understanding is that in Javascript the primitive value of a date object is the number of milliseconds after (or before) midnight on 1st Jan, 1970; in Google Spreadsheets, the primitive value of a date is 0 (zero) for midnight on 30th Dec, 1899, and each day thereafter equals 1 (so, for example, 7 hours is equal to 7 divided by 24).</p>
  
  <p>I tried this function:</p>
  
  <pre><code>function date2Serial(date) {
    return (date.getTime() + 2209161600000) / 86400000;
    // 2209161600000 = milliseconds between 1899-12-30 and 1970-01-01
    // 86400000 = milliseconds in a day
    // question "on the side" - is there any essential difference between
    //  .getTime() and .valueOf()?
  }
  </code></pre>
  
  <p>And this returns a value that is a bit off - and I think the "off" corresponds with my time zone difference from GMT. So even though both the Spreadsheet Settings and Project Properties are set to GMT+10 in my case, it seems that getTime() operates on GMT.</p>
  
  <p>So for example:</p>
  
  <pre><code>A1: 16/09/2012 06:00:00
  A2: =date2serial(A1)
  A3: =VALUE(A1)
  </code></pre>
  
  <p>A2 returns 41167.8333, where A3 returns 41168.25.</p>
  
  <p>Now I tried using the <a href="https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/Date/getTimezoneOffset" rel="nofollow">getTimezoneOffset()</a> method, which appeared to correct the issue:</p>
  
  <pre><code>function date2Serial(date) {
    return (date.getTime() + 2209161600000 - (date.getTimezoneOffset() * 60000)) / 86400000;
  }
  </code></pre>
  
  <p>However this relies on the Project Properties time zone being correct, as opposed to the Spreadsheet Settings time zone (it is actually possible to set each of these differently).</p>
  
  <p>My question:</p>
  
  <p>Is this behaviour expected, and the "adjustment" for time zone necessary? Or should the getTime() (and all the other methods like getHours(), getMinutes() and so on) operate on the user's set time zone rather than GMT/UTC, and the "adjustment" is actually a workaround for a bug?</p>