Calc auto-changing EE dates to YYYY on save

shakazulu · August 8, 2021, 8:40am

I have dates conditionally formatted to read as Year Month after 1700, AD Year from 0-1700, and Year BC before 1 AD (eg: 2021 Aug, AD 500, 500 BC; interestingly, there is no Year 0 in the Gregorian/Julian calendars, with 1 BC hopping right to AD 1 on New Year’s Day). It’s done using this formula:

[>-73046]JJJJ MMM;[<-693595]EE" BC";"AD "EE

Those numbers are the values for the days December 31, 1699 and January 1, 1 AD, respectively. EE is code for “era”: Number Format Codes - LibreOffice Help. Upon restarting that formula always changes to:

[>-73046]JJJJ MMM;[<-693593]YYYY" BC";"AD "YYYY

… not affecting recent, post-1700 dates but rendering the AD/BC ones with leading zeros and negative signs, so as AD 0500 and -0500 BC from the example above. Changing the formula back and restarting Calc results in the same problem.

Changing the Date Acceptance Patterns as recommended on Why does LO change a date format? - #4 by Lupp didn’t fix things.

Help? I feel there must be some change-on-save setting I must be missing, some simple checkbox I need to check or uncheck somewhere…

Lupp · August 8, 2021, 9:51am

I dont remember to ever have recommended this. There may have happened a mix-up with the respective comment by @petermau in the linked thread…

erAck · August 8, 2021, 12:50pm

Attempting to use EE as being related to an era of the Gregorian calendar is due to the format code help being imprecise (apart from that you link to a very old help version). The code for era is GG (abbreviated) or GGG (full name). See Date and Time Formats in the help.

E and EE are relevant only in locales that have (at least) two calendars defined and allow to switch to that second calendar within a format code, for example with ja-JP the Japanese Gengou calendar. For locales that have only one (Gregorian) calendar defined there is no difference between using EE or YYYY and hence when loading the format description from an ODF file the format code generated is YYYY. Apart from that without a GG or GGG era code for negative years the minus sign is displayed and even with GG or GGG always the 4 digit year (the latter could be considered a bug). The format code you actually want is
[>=-73046]YYYY MMM;[<-693593]YYYY GG;GG YYYY
or for your German use
[>=-73046]JJJJ MMM;[<-693593]JJJJ GG;GG JJJJ
(which still display leading zero 4 digit years, but you can’t get around that at the moment).

erAck · August 8, 2021, 1:08pm

Though seeing German there the actual era strings would be “v. Chr.” and “n. Chr.”, you may want to force an English locale to get “AD” and “BC”.

erAck · August 8, 2021, 1:54pm

FYI, just submitted code to fix that, YYYY used with any G, GG or GGG will not prepend leading 0, may be available in 7.2.1

shakazulu · August 17, 2021, 11:58am

Great, thanks. Can you show me where you submitted this change? I’d like to both see it and how pull requests work with this program.

erAck · August 17, 2021, 8:48pm

@shakazulu There’s the change in Gerrit and for general information see get started with development and specifically on submitting patches with Gerrit.

Lupp · August 8, 2021, 10:25am

From some experience and "research, NOT from “reliable knowledge”!

The Gregorian calendar never was backported, and the Julian calendar originally didn’t number the years the way Christian tradition later did. And a year 0 never was introduced. That’s just a minor one of the many inconsitencies in the field. The zero only was eventually accepted as a number (NOT synonymous to “digit”!) a few hundred years ago.
Spreadsheets aren’t made emphasizing probable special needs concerning subjects of history. Nonetheless the usage of negative serial numbers is supported counting down one per day.
In addition the one and only fully developed NumberFormat (ISO 8601 extended) for dates treats the backport of the Julian calendar, and the hiatus by the Gregorian calendar correctly. The different toys also available as so-called date formats don’t (or should be suspected not to do). ~~Also the function YEAR() isn’t applicable tp BC e.g.~~
Base your special (stubborn) textual date formats on an interim formatting with the code YYYY-MM-DD applying string manipulation to the result. I don’t think there is a way to get what you want with the help of standard number formats…

[Edit 2021-08-08 about 12:15 GMT]
The attachment is containing a suggestion how to handle the specific custom date format using strings. The numeric representation of the dates may be hidden in a sheet then.
disask66774historicalDateFormats_1.ods (25.4 KB)
[/Edit]

mikekaganski · August 8, 2021, 11:05am

I assume this is a typo, and “after 1500” should had been “after 1700”; and additionally you need to use [>=-73046] (note the =, covering 1700-01-01).

I repro this problem using ODS and version 7.2.0.2. You need to file a bug report.

@erAck might have a deeper insight here.

shakazulu · August 9, 2021, 1:48pm

Thanks for noting the typos @mikekaganski and @erAck. It is German indeed so the example should’ve been either YYYY or JJJJ across the board, as goes with the 1500/1700 cut-off (I’d modified it from one to the other in the process of writing this question). The “>=” has been added as well, thank you.

@erAck your first solution is close. The leading zeroes are still annoying but at least there’s no “-” in front of BC dates. The GG placement also always left the BC/AD abbreviation at the end (the standard seems to be eg. 500 BC and AD 500). I tried this with forcing an English locale but it didn’t make a difference (apart from the v/n. Chr. you note). You are fantastic for submitting a bug fix for the leading zero! Any idea when 7.2.1 might be released?

erAck · August 9, 2021, 7:06pm

Week 34 (Aug 23, 2021 - Aug 29, 2021).

shakazulu · August 17, 2021, 11:56am

Thank you.

erAck · August 17, 2021, 8:54pm

Sorry, mislead, Week 34 is the branch-off, release is scheduled for Week 37, Sep 13, 2021 - Sep 19, 2021 (see link I gave earlier).

shakazulu · August 9, 2021, 1:49pm

That was very neat to discover, @Lupp, about the ten-day Gregorian hiatus, October 4-15, 1582. I Googled but couldn’t find more information on it other than it being the reason astronomers use the Julian for back-tracking for this reason. Do you know why this happened? I also love the term “Zero-denier Discontinuity”, this should be the official name for what happened.

I am using this spreadsheet to list my library which includes some early texts from the Romans and Greeks and Chinese, ergo wanting to reach back as far as maybe 1000 BC I can anticipate. My oldest so far is The Art of War (500 BC), but I’m sure there will be older as I just started this project.

Lupp · August 9, 2021, 5:42pm

Popes in times of Gregor XIII were also interested in astronomy, and had their “scientific advisors” (as Chinese emperors had; some of them Jesuites at the time if I’m right).
However, a calendar always wasn’t made as a base for astronomy but reversely astronomy was inmportant to maintain a kind of calendar helping to organize agriculture and public life. For Gregor this mainly meant to serve as a means of computing (and fixing in a sense) christian holidays.
Astronomers were expected to be clever enough to find their ways without papal guidance. For astronomy a fix year of 365.25 days is preferable over the hip-hop of leap years and omitted leap years and omitted omission. The drifting of real-world-events like equinox through that kind of calendar is much easier to take in account then.
However, astronomers never were clever enough to abandon the funny /12/60/60 subdivison of the ordinary halfday nor the /60/60/60 subdivision of the natural unit of angles. 4000 years in the ~~bones~~ brains. Sigh. There still are also /1760/3/12 subdivisions in a field. Did you know?

shakazulu · August 17, 2021, 7:29pm

Ah interesting, so it was an accumulation of .25 day increments over centuries of missed leap years they had to catch up with? This reminds me of the one-off astrological discrepancy Bill Nye once mentioned, Bill Nye on Astrology - YouTube.

Lupp · August 17, 2021, 10:38pm

There were no missing leap years, but too many.
A shift from thursday, 1582-10-4 to friday 1582-10-15.instead of 1582-10-05 (adding 10 to the day number, but keeping the sequence of weekdays) was as if 10 leap years were cancelled… The attempted positioning of an astrononomical event (spring equinox) in the adimistered calendar should again be about as it was when Christian Easter was regulated in AD 325. The holidays bound to easter, and the holidays with fix calenadric dates shouldn’t drift too much (changing distance).

SM_Riga · August 18, 2021, 7:24pm

Thanks for tiny quiz Miles/yards/feet/inches I suppose.

Lupp · August 18, 2021, 8:08pm