Ask Your Question

Calc TEXT Time Zone Formatting?

asked 2021-02-21 19:16:28 +0100

CompSciSarah gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2021-02-22 14:31:23 +0100

erAck gravatar image

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.

edit flag offensive delete link 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?

Lupp gravatar imageLupp ( 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.

erAck gravatar imageerAck ( 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.

Lupp gravatar imageLupp ( 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).

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

answered 2021-02-21 21:42:28 +0100

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"

edit flag offensive delete link 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.

keme gravatar imagekeme ( 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 :)

CompSciSarah gravatar imageCompSciSarah ( 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.

keme gravatar imagekeme ( 2021-02-22 06:50:37 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-02-21 19:16:28 +0100

Seen: 41 times

Last updated: Feb 22