CODEWITHSUNDEEP
google-sheetstimezonestring-formattingutcepoch
wivku
wivku

Reputation: 2703

Google spreadsheet - how to determine timezone using function

How to use a formula to determine the current timezone? The formula I use gives an unexpected result.

My spreadsheet settings (File > Spreadsheet settings...):

Time zone: (GMT+01:00) Amsterdam

The formula I used:

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

This gives:

12:47 GMT

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

Any suggestions?

I need this, so I can determine UTC time and convert to Epoch time

"UTC time" - DATE(1970,1,1)*24*60*60

Upvotes: 5

Views: 13028

Answers (3)

player0
player0

Reputation: 1

nowadays in 2024, 11 years after posting this question we can try to debunk this mystery.

since we can't go back in time (unless someone would provide a spreadsheet from that era with that formula variation) to debug it, we can only rely on today's observation:

in time of this writing, TEXT formula does not support timezones

enter image description here

it is however worth mentioning, that google sheets supports custom timezone offsets directly in settings:

enter image description here

on top of that, there is a hidden legacy formula that understands timezone commands, but unfortunately the whole feature is broken and resembles the scenario from OP's question:

=TEXT.LEGACY(NOW(); "HH:MM:SS Z")
=TEXT.LEGACY(NOW(); "hh:mm:ss z")

enter image description here

as it can be seen, the output is always GMT or +0000 only, and it can't be modified (nor timezone settings from File > Settings > Timezone affects it). while it is true that you could use:

=TEXT.LEGACY(NOW(); "hh:mm:ss z+1")

to output:

03:53:01 GMT+1

it is only a visual trick as the time is not affected anyhow by it, same as

=TEXT.LEGACY(NOW(); "hh:mm:ss +0100")

will yield output:

03:53:01 +0100

btw also achievable with regular TEXT formula with this command where zeros are just escaped with backslash:

=TEXT(NOW(); "HH:MM:SS +\01\0\0")

be warned, that TEXT.LEGACY and other legacy formulae should be used with caution (or not used at all) as they may differ to their regular counterparts:

enter image description here

enter image description here

enter image description here

as can be seen from examples, TEXT.LEGACY may be inaccurate and does not support milliseconds as 000 but instead as SSS

so what are LEGACY functions for?

functions suffixed with .LEGACY indicate they are older versions, kept as insurance that older spreadsheets (or those imported from Excel or other sources/platforms) continue to function correctly without breaking due to changes or updates in the newer function implementations. they may be discontinued/removed anytime without further notice and their whole purpose lays in the realm of backward compatibility, acting as ghost functions without any official documentation.

now to answer the question... if NOW() time is 12:47 for visual purposes you can use:

=TEXT(NOW(); "hh:mm +\01\0\0")
=TEXT(NOW(); "hh:mm \G\MT+1")

for static time there is no need to escape zeros with backslash:

=TEXT("12:47"; "hh:mm +0100")
=TEXT(0,5326388889; "hh:mm +0100")

enter image description here

to actually change NOW() time to 13:47 timezone, you either use timezone in spreadsheet settings or apply some math:

=TEXT(NOW()+1/24; "hh:mm +\01\0\0")
=TEXT(NOW()+1/24; "hh:mm \G\MT+1")

to convert NOW() time to Epoch time, we can use following formula, but first we need to set timezone to GMT(+00:00) GMT (no daylight saving) this will ensure your NOW() is in pure UTC format, as this is the correct way how to determine UTC time in google sheets:

=(NOW()-25569)*86400

enter image description here

to convert back, you can use:

=EPOCHTODATE(1716690275)

enter image description here

so far it's easy, but what if you have your times recorded in your local timezone and want them to convert into proper UTC correctly, with precise DST (daylight saving time) offset, so that the captured events are backward traceable on a universal timeline... see: https://stackoverflow.com/a/78444708/5632629

Upvotes: 0

Robbie
Robbie

Reputation: 597

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

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

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

Hope this helps,

Robbie

Upvotes: 0

PsychoFish
PsychoFish

Reputation: 1639

You can't do that using formulas w/o javascript checking your local timezone. As per this form:

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

timezone formatting stuff is not supported in TEXT. This spreadsheet was done by one of Stackoverflow contributors, it is not mine.

So... script?

Upvotes: 0

Related Questions