# Calc TEXT Time Zone Formatting?

I'm interesteed in adding the local machine's timezone to a TEXT function.

How would I do this? (And also, is there a page of formatting options for TEXT)? I searched for a bit but couldn't find it.

Something like TEXT(NOW(),"MM/DD/YYYY HH:MM:SS"), but with a space + time zone at end (EST)

edit retag close merge delete

Sort by » oldest newest most voted

While you can hard format adding a fixed whatever string, all date+time values are timezone agnostic. Specifically there is no (built-in) way to automatically recognize and format any timezone nor a change of timezone or DST. This is due to the fact that all major spreadsheet implementations use a numeric floating-point value to express date+time in days since a given null date, which does not provide means to carry this information.

more

There also seems to be no way to get the information in addition. That looks really annoying to me. Think of a guy actually needing time zone information for global cooperation and synchronization. The other guy wants to make a work-time sheet copíng reasonably with the DST nonsense. What should they do?

( 2021-02-22 20:02:28 +0100 )edit

Write an extension..

I have no idea if this Java Time Zones extension helps, reading its unformatted flow text description is kind of confusing.

( 2021-02-25 16:07:53 +0100 )edit

I'm not good in reading (understanding) Java, and if you find something to be confusing, there's no hope concerning myself.
However, I'm considering for what reason it's not a matter of course for any system and time-server, to give easy access to (at least) 3 time values: UTC, zone time (or offset only), administrated locale time (including time-depending silly DST offset where established).
I cannot imagine how somebody needing to be just a bit precise about time can use an ordinary computer under the given conditions as far as I know them.

( 2021-02-25 16:25:38 +0100 )edit

The necessary structures are usually available (at least on modern systems) and along with tzdata the offset from UTC for a given time and location/timezone is known (unless in future (and sometimes even the very recent past) because that DST nonsense keeps changing and even timezones can).

( 2021-02-25 21:33:24 +0100 )edit

Hello, you can tried with =TEXT(NOW(),"MM/DD/YYYY")&" "&TEXT(NOW(),"HH:MM:SS")&" (EST)" and this is the libre office result "02/21/2021 14:40:49 EST"

more

You can achieve the same by =TEXT(NOW();"MM/DD/YYYY_ HH:MM:SS (\E\S\T)") which calls NOW() only once. This avoids the (extremely rare) error that may occur at midnight, where the first NOW() is before midnight and the second is after, yielding a timestamp which is 24 hours off.

Having the timezone outside of the number format makes sense.
=TEXT(NOW();"MM/DD/YYYY_ HH:MM:SS")&" (EST)" is perhaps optimal.

Letters in a format string need to be "escaped" (the backslashes in the first formula) to appear verbatim. Otherwise they may accidentally act as a number placeholder. Your formula is easer to read with this variant, and you can keep the timzone info elsewhere and fetch it individually for your time formulas.

( 2021-02-21 22:17:41 +0100 )edit

This dynamically changes timezone? What happens during daylight time -> EDT (or if someone was in pacific time)? It looks like a static string :)

( 2021-02-21 22:37:44 +0100 )edit

It looks like a static string

Correct. It is a manual entry of the particular TZ you mentioned, and does not fetch a setting from your system, which is perhaps your primary requirement.

( 2021-02-22 06:50:37 +0100 )edit