I've just learned that "Excel has no native concept of universal time. All times are specified in the local time zone. Excel limitations prevent specifying true absolute dates." [1] This is quite surprising to me. When people shared Excel files via email or FTP, how did geographically distributed teams agree on what a given timestamp meant? If Excel stores only local times, then when a team member from Germany sends a file to a colleague in Tokyo or in the US, dates could be off by one day, times will be off by many hours etc. I'm trying to use a library (SheetJS, link below) that generates XLS or ODS files, and the author claims it's impossible to store datetimes in UTC in Excel files: "Following Excel, this library treats all dates as relative to local time zone". Does LibreCalc improve on this situation? Either way, can the timezone aspect please be documented somewhere, e.g. at https://help.libreoffice.org/Calc/Date_and_Time_Functions ? We can then point the author of the library to the appropriate documentation, to hopefully add support for universal time for ODS files. [1]: https://docs.sheetjs.com/#dates
It appears LibreCalc doesn't have the concept of universal time, or time zone, either. Please see bug #121851.
I don't think we need to document a feature that is not implemented. Closing as RESOLVED WONTFIX This can be reopen once bug 121851 got implemented
I agree that the timezone functionality is missing. On the other hand, all modern collaboration software does support timezones, so lacking support for them is unusual. There are real-life use cases in which the lack of timezones can cause problems, and I've added another one at https://bugs.documentfoundation.org/show_bug.cgi?id=121851#c3. According to the https://en.wikipedia.org/wiki/Principle_of_least_astonishment, I would strongly suggest that we document this unusual behavior (lacking timezone support), in order to manage expectations correctly. Happy to write the documentation if provided with the appropriate MediaWiki editing credentials.
(In reply to Dan Dascalescu from comment #3) > I agree that the timezone functionality is missing. > > On the other hand, all modern collaboration software does support timezones, > so lacking support for them is unusual. > > There are real-life use cases in which the lack of timezones can cause > problems, and I've added another one at > https://bugs.documentfoundation.org/show_bug.cgi?id=121851#c3. > > According to the > https://en.wikipedia.org/wiki/Principle_of_least_astonishment, I would > strongly suggest that we document this unusual behavior (lacking timezone > support), in order to manage expectations correctly. it's already covered in bug 121851, no need to have 2 bugs for the same thing. *** This bug has been marked as a duplicate of bug 121851 ***
> it's already covered in bug 121851, no need to have 2 bugs for the same thing. What exactly is covered in bug 121851? This issue (121781) is different. It's about documenting the current lack of timezone information for date/time cells. What I'm proposing is updating the wiki page https://help.libreoffice.org/Calc/Date_and_Time_Functions in order to inform readers of this Fixing bug 121851 may take a very long time, but updating the wiki can be done immediately. To put it another way: imagine I had never filed bug 121851, or that it was closed. Would you still close this bug and be against updating the wiki?
Fair enough, changing it to Documentation and NEW...
Olivier Hallot committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/+/074bfa0b64aafdfad465293c689e17230b4ccedc%5E%21 tdf#121781 Clarify TZ info in Calc functions
Note that the old wiki-based help pages are obsolete and won’t be updated further. The new page (where the above patch is going to be reflected) is https://help.libreoffice.org/6.3/en-US/text/scalc/01/04060102.html