[SOLVED] LibreOffice Calc Date Arithmetic Hassle [NOT a Manjaro fault]

Can anyone please shed light on this ostensibly useless misbehaviour / dysfunction of LibreOffice Calc wrt date arithmetic, when the data is copied & pasted from a web page?

The web source:


.
.
I explicitly told Calc to detect dates:
20180326_005
.
.
LO Calc ignored me, hence dates were not correctly formatted, & the simple date arithmetic failed:
20180326_006
.
.
Manually setting proper date format in LO Calc still failed:

.
.
The ONLY way i could bludgeon LO Calc into finally doing the date arithmetic was to manually edit the respective cells. This is preposterous.
20180326_008
.
.
In comparison, WPS Office Spreadsheets manages it all correctly, & automatically [NO manual intervention was needed by me]:
20180326_002
.
.
Similarly, MS Office Excel manages it all correctly, & automatically [NO manual intervention was needed by me]:
20180326_004
.
.
Am i somehow doing it all wrong in LO Calc, or is this indeed a serious defect?

LibreOffice definitely still has some… quirks. Are you using Still (5.*) or Fresh (6.*)? (If it’s Still, try switching to Fresh).

WPS is an odd one. I’m almost convinced Kingsoft is an “undercover Microsoft” which targets the Chinese market with a free Office suite to reduce piracy. I can’t think of any other reason it works almost identically to MS Office.

Ta for yr fast reply. Gahhh, i am a dope, i specifically told myself as i began writing my post to mention my LO version, then promptly forgot again. It’s Fresh, not Still. As my Tower’s Manjaro KDE is in Stable branch, my LO = 6.0.2-1.

There’s much to like about WPS Office, & yes given that i used to be an intensive MS Office 2010 Pro user in Win7 until changing to Linux in 2014 the commonalities between the two suites are astonishing. That said, WPS Office also has its own weirdnesses too. Most of the time, since i adapted to the many usage mode differences & idiosyncracies of LO Office i generally prefer it above the others, but every now & then i encounter some apparently cretinous bit of mis-design or dysfunction like this post, which makes me blow a foofle-valve :smile:

It is a problem of the format of the date. It should be YYYY-MM-DD or YY-MM-DD. But your dates are DD-MM-YYYY.
I made a small table with different formats of dates. See here:

The first three are YYYY-MM-DD and then two dates YY-MM-DD (but in the last century). The next two are also YY-MM-DD but in this century and the last ones are DD-MM-YYYY.

LibreOffice did everything right for the first five examples:
image

The last four are just formatted as text because LO can’t recognize the format.

Thanks for replying.

I’m Australian; our standard date formats are dd-mm-yy or dd-mm-yyyy or dd.mm.yy or dd.mm.yyyy or dd/mm/yy or dd/mm/yyyy.

I will never ever voluntarily use american half-backwards format [eg, mm/dd/yyyy], & try very hard to avoid needing to use fully reversed format [eg, yyyy/mm/dd]. Moreover as i demonstrated, both MS Office & WPS Office entirely handle my regional formatting properly. Also as i demonstrated, LO Calc itself also handles my regional format correctly for the arithmetic if slashes are used [but that requires me to have to tediously manually edit each relevant cell, whereas the other suites do it themselves].

My whinge is that if i am to continue using LO Calc to perform simple date arithmetic on data sourced from that website [my ISP account portal] then apparently i will need to do manual editing, whereas both other suites handle it elegantly & automatically.

I appreciate the time you kindly took to test & reply :slight_smile:

I didn’t want to say that your way of writing dates is wrong but LO just uses a set of different date formats and dd-mm-yyyy is not part of it. I tried to integrate that format as user-defined but LO doesn’t take it in account when pasting a date formatted this way into a spreadsheet.

Yes i understand … but with respect, it appears that we are simply both agreeing with my OP’s primary conclusion… ie, LO Calc is either badly designed or otherwise has a bug, regarding date arithmetic with some formats, & this bug or design defect is not present in MS Office or WPS Office.

Btw, here’s another quirk of LO Calc. As i have unfortunately discovered it does not handle date arithmetic with that local dash format, but it does nonetheless still manage to correctly Chart this data anyway:

Did you use the correct local settings in Options - Language Settings - Languages - Local settings ?

It should look like this ( I don’t have the UserInterface avaible). It sould also be in English (Austrailia) for you.

You also need to look at Date acceptance patterns: D/M/Y should match 03/02/2018 .

With this settings you can do this

2

But if LO detect it wrong and you format the cell, it sometimes adds a ’ in front the numbers. It makes from a valid number a text string.

3

– edit
Just sah your source is DD-MM-YYYY. So add to Date acceptance patterns: D-M-Y
Then it will convert to your local standard.

2 Likes

Thanks for your kind help.

Yes, & consequently at the bottom of the Calc UI:

20180327_002

I’m sorry but that’s irrelevant. I have already shown that Calc has no trouble doing the Date arithmetic when “slash” [ie, “/”] dates formatting is used. The problem arises specifically when “dash” [ie, “-”] dates formatting is used … & to repeat, neither Excel nor WPS fails here; only Calc fails. Also to repeat/clarify, using “dashes” in the dates formatting is not my personal preference, but unfortunately the website that is the source of the data i will be cumulatively adding to my spreadsheet over coming months does use the dash format.

I agree with you, & have been bitten by that subtle problem various times over the years. However this is one of the possibilities i have checked [several times] as i have tried to solve the current problem; there is no " ’ " present:

20180327_005

I’m not certain that i understand what you are telling me here, sorry. My best interpretation is that you were advising me to create a custom format, which i did like this:

…but it still makes no difference; Calc continues to fail at the date arithmetic:

20180327_004

I know this is just a workaround for your original issue, but you can use 'Find and ‘Replace’ to replace the dashes.

Select all cells that you want to change.
Ctrl-H for the ‘Find and Replace’ dialogue.
Find: -, Replace: /, Replace All.
Done.

2 Likes

Indeed, but it is a valuable tactic, thank you.

I keep hoping that Calc really can cope with that format & it’s just me at fault for not yet discovering the “magic trick”, but my hopes are rapidly fading now.

EDIT: Wow, it works a treat!! Yay. Clearly not as good as if Calc was as natively smart as Excel & WPS, but it’s a really nice pragmatic workaround under the circumstances. Thank you very much.

Now that the matter is settled, I hope this OT will not pique anyone. :slightly_smiling_face:

No. Nothing to do with Microsoft. The Chinese just copy everything. Just like that student who gets a A+ copying from that C- student. :joy:

The international ISO standard is yyyymmdd.
Just that nobody follows it.
Furlongs and stones, like in horse races.
Maybe we should go back to using Li and jin.

It is not. So far there is a good workaround, but not a solution.

How about this, applying it to the raw data you copy pasted.
https://ask.libreoffice.org/en/question/87210/calc-conversion-text-to-date/

1 Like

Thank you!

Now that is a very nice trick indeed. It certainly does produce the required outcome.

Would you regard me as churlish if i was to remark, however, that this cunning technique, similar to the “find - replace” one of @muser, nevertheless still falls into the “workaround” category, not the “solution” category. By this i mean that the apparently underlying defect or otherwise design omission of Calc remains unfixed. At the risk of boring you all i must repeat… neither Excel or WPS needs any workaround at all… with both of them i simply paste in the raw data copied from the source website, & their dates arithmetic instantly works… i have to do NO post-processing with them.

I’m sorry but this is something different. I meant the “Locale settings” that can be found in Tools -> Options… -> Language Settings -> Language.

At the bottom is the Language for this Document. But this usually applies for spelling correction and not much more.

After you added D-M-Y to Date acceptance patterns it will only apply to new insert text. This means you need to copy and past in your original text again. It will only used for new text. You can also add more Patterns. It then will automatically changed to the standard.

After that you can change the cell format to everything you want. Even Back to DD-MM-YYYY, if you want to. But at first LO needs to know that this is a date and this needs to go thru the “Data acceptance patterns”. But this applies only to new data pasted in.

3 Likes

You are brilliant… BRILLIANT!! Thank you, thank you, vielen dank :star_struck: You have solved it. This is the solution, not just a workaround. This is exactly what i was looking for [= no post-processing needed], & it came down to an important part of configuring LO that i did not previously know about.

I did / do have the basic region setting correct already:


…but as we can see i had not ever edited the default “Date acceptance patterns” to add this additional Australian format… i never knew about this part of the Settings [even though i have looked at this dialog box dozens of times over the past years, i always somehow failed to notice this field, blush].

Once i did what you have guided me:
20180327_008
…& then re-pasted the source data as you advised, Calc finally responded immediately with the correct date arithmetic result… just like Excel & WPS.
20180327_009

This is exactly what i was hoping to find, from my thread… an outcome whereby the error [of ignorance] was mine, not LO Calc’s. This was important learning for me, & i am extremely grateful to you. :smile:

1 Like

I think something like that should work without user interaction. It is nice that someone can change it but there are not that many ways to format a date. LO should detect a data in any common format like other spreadsheet tools.

But it is nice to hear that it works for you now.

Actually i totally agree with you. I have never needed to “pre-configure” that in either Excel or WPS, they both simply worked the right way “out of the box”. Whilst i do still feel a bit of a dope for never realising that part of LO"s Settings before, in my defence i never imagined that it was something i would “have” to tell the software to do… i just assumed that LO would already manage such things like those other programs do.

I can’t speak for WPS (never used it), but M$ Excel has allot of quirks too. I’m a heavy and advanced Excel user (at work) and I can’t count the times I’m frustrated about it.

It has some bugs that are persistent for years (and still not solved), and sometimes it acts in a very strange and inconsistent manner, especially when working in a multilingual environment and with many different files. And I hate it that it is the “the standard” in an office environment…

I just wanted to point this out and to set things in balance… :joy::joy::joy:

Forum kindly sponsored by