Google spreadsheet - how to determine the time zone using the function

Question

How to use the formula to determine the current time zone? The formula used gives an unexpected result.

Spreadsheet Settings (File> Spreadsheet Settings ...): Time Zone: (GMT + 01: 00) Amsterdam

Used formula: = TEXT (NOW (), "HH: mm z")

It gives: 12:47 GMT

Local time is 12:47, I expect the formula to show: " 12:47 GMT + 1. " I also tried Z instead of z, which gives "12:47 +0000", I would expect +1.

Any suggestions?

I need this so that I can determine the UTC time and convert it to Epoch time ("UTC time" - DATE (1970,1,1) * 24 * 60 * 60)

Answer 1

= TEXT (NOW () + x / 24, "DD / MM / YYYY HH: mm: ss")

= TEXT (NOW () + 8/24, "DD / MM / YYYY HH: mm: ss")

(+ x / 24) after the NOW function will add x hours (in my case 8) to the standard time. NOW () + 1 will give you time tomorrow, so working as bakc or forward, you can set a formula to provide different time zones.

Hope this helps,

Robbie

Answer 2

You cannot do this with formulas without checking JavaScript in your local time zone. According to this form:

https://docs.google.com/a/codeproject.com/spreadsheet/ccc?key=0AqhqY231XZd3cFBiY2VqeWdmNWdaX25zN2lpekthQlE&hl=en_US#gid=4

Timezone formatting information is not supported in TEXT . This table was made by one of the members of Stackoverflow, this is not mine.

So ... script?