Compatibility issues with Microsoft Excel 2019

English
#1

Coming from Count hours per period from a large list I have now several solutions working fine in Libreoffice but not compatible with Microsoft Office.
This thread should be about compatibility and if there are more portable functions for both.

Hours-Testproject_02.2.ods (64.9 KB)

1 tab per yearly period, counting time durations of actions, each tab referencing the previous tab residue, some conditional formatting for alternating row colors and bad values.

Working perfectly so far in Libreoffice Version: 24.2.4.2 (X86_64)

Saving as Excel 2007-365(.xlsx).

What is not working in MS Office:

A1: Text field referencing the file name

Working alternative for Excel:

=LINKS(TEIL(ZELLE("Dateiname"; A1); FINDEN("["; ZELLE("Dateiname"; A1))+1; FINDEN("]"; ZELLE("Dateiname"; A1))-FINDEN("["; ZELLE("Dateiname"; A1))-1);
FINDEN("."; TEIL(ZELLE("Dateiname"; A1); FINDEN("["; ZELLE("Dateiname"; A1))+1; FINDEN("]"; ZELLE("Dateiname"; A1))-FINDEN("["; ZELLE("Dateiname"; A1))-1))-1)

Back in LO:

=LEFT(MID(CELL("Dateiname"; A1); FIND("["; CELL("Dateiname"; A1))+1; FIND("]"; CELL("Dateiname"; A1))-FIND("["; CELL("Dateiname"; A1))-1);
FIND("."; MID(CELL("Dateiname"; A1); FIND("["; CELL("Dateiname"; A1))+1; FIND("]"; CELL("Dateiname"; A1))-FIND("["; CELL("Dateiname"; A1))-1))-1)

…not working. Is there any compatible alternative?

A3: Text field referencing the tab name

Working alternative for Excel:

=RECHTS(ZELLE("Dateiname"; A1); LÄNGE(ZELLE("Dateiname"; A1)) - FINDEN("]"; ZELLE("Dateiname"; A1)))

Back in Libreoffice:

=RIGHT(CELL("Dateiname"; A1); LEN(CELL("Dateiname"; A1)) - FIND("]"; CELL("Dateiname"; A1)))

…not working. Is there any compatible alternative?

C3: Referencing the previous tab residue.

In .ods: =INDIRECT(TEXT(A3 - 1; "0") & ".$E$4") Working
In .xlsx: =INDIREKT(TEXT(A3 - 1; "0") & ".$E$4") Not working

Working solution for Excel:
=INDIREKT(TEXT(A3 - 1; "0") & "!$E$4")

Back in LO, this is also working. So this might be worth a bug report to improve format migration in LO?

E24-25: Negative time values

=E23-(C24-D23+D24)

In LO I formatted these in red via conditional formatting.
In Excel the negative values are shown as #######.

As alternative in Excel I tried:

=WENN(E24-(C25-D24+D25)<0; "-" & TEXT(ABS(E24-(C25-D24+D25)); "h:mm"); TEXT(E24-(C25-D24+D25); "h:mm"))

Back in Libreoffice:

=IF(E23-(C24-D23+D24)<0; "-" & TEXT(ABS(E23-(C24-D23+D24)); "h:mm"); TEXT(E23-(C24-D23+D24); "h:mm"))

Interesting, back in LO this is working, but in Excel only the first calculation works, all following rows can’t calulcate with the text format.

Formatting and text color
In LO there is a grey-font format style for calculated values in columns Residue and Balance, named “Calculated”. In Excel the fonts are still grey. Back in LO opening the .xlsx the black fonts in Duration column are blue and the grey fonts are green now?

[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck ; also, it would had helped others if you consistently used English function names instead of a mix with German.]

Count hours per period from a large list
#21

sheetSeparator.ods (22.4 KB)
Have a look at the names (Ctrl+F3).

#40

Something else, about Conditional formatting.
Why did the .ods to .xlsx migration change all Conditional Formatting from Style AlternatingDark to ConditionalStyle_8 ConditionalStyle_9 ConditionalStyle 10, duplicated for each tab?

The numbers format somehow has been integrated in the new styles, because in .ods I had the number formats set directly.

#2

The [ and ] in file names is an Excel legacy speciality, extracting a file name or sheet name from CELL() that way does not work in LO. Your code would have to differentiate whether it runs in Excel or in Calc. That could be done by observing the first character of the file name designator, which in Calc is always the ' apostrophe. Extracting the file name could be fairly easy with regular expressions in SEARCH(), but Excel does not support that, or the REGEX() function, which Excel doesn’t support either but came up with its own REGEXTEST, REGEXEXTRACT, REGEXREPLACE functions instead (see tdf#161271).

Maybe some macro wizard here could come up with a VBA compatibility user defined function.

Because in Excel the sheet separator is ! exclamation mark and in Calc native it is . dot. Excel does not understand anything else than !, but Calc when importing .xlsx switches to also accepting the Excel reference notation in functions like INDIRECT().

No bug. There’s nothing to improve on LO side. If you have to work with both applications on files and produce string literal references for INDIRECT() including the sheet name then use the Excel A1 reference notation and switch Tools → Options → Calc → Formula, Detailed Calculation Settings, Custom, Details, Reference syntax for string reference to Excel A1.

Btw, the expression TEXT(A3 - 1; "0") may or may not work depending on detailed calculation settings, as the value in A3 is not numeric but text from a field. Rather use VALUE(A3)-1 and in Calc the TEXT() formatting is superfluous but with Excel might be needed. So this could be

=INDIRECT(TEXT(VALUE(A3) - 1; "0") & "!$E$4")

IIRC Excel can’t cope with negative time values.

2 Likes
#3

It does seem to accept some translated keywords: e.g. for "filename" in the CELL() function it could be: "NomFichier" (fr), "Filenév" (hu) and "Dateiname" (de).

The INFO() function accepts French and Hungarian names.

#4

CELL function
CELL(“InfoType” [; Reference])
InfoType is the character string that specifies the type of information. The character string is always in English. Upper or lower case is optional.

#5

Oh shit, that’s indeed implemented. Sorry.

#7

When only using LO, saving as .xlsx format, the formular
=INDIRECT(TEXT(A3 - 1; “0”) & “!$E$4”)
doesn’t work.

#8

Now I have set this configuration in LO and tried that formular, but it’s failing.
Hours-Testproject_02.3.xlsx (35.2 KB)

#9

… even after reopening the XLSX (which is the moment when the syntax changes, not the moment of save - unless you explicitly change the syntax in options)?

#10

Yes, still failing after re-opening.

#11

Works fine here. Including your file. Especially putting something to A3, which is used in your formula, but is not set.

#12

Sorry, you are right. Working here too now. A3 was holding the text field with the tab name “2024” which was removed when saving as .xlsx. For this minor issue I don’t have a portable solution yet, so creating a new tab 2026 will require to also set the name text 2026 in A3.

#13

If this is a global option of my LO setup, does that mean after switching the formular dialect to Excel A1, I need to switch all my LO setups to this setting and I can’t work with original files anymore using the Calc A1? I would prefer having the Excel A1 formular style only applied when using .xlsx files as common portable format with Excel.

#14

If you refer to the current sheet every time, then it’s completely unclear why would you need to prefix the sheet name in the formula in the first place, with all these differences between Calc and Excel syntax, when in both, simple =INDIRECT("$E$4") would work?

Aha, I see - you refer to the previous year. Then why not use formula without INDIRECT, making use of relative addressing (in the hope that your year sheets follow in natural order):

='2023'.$E$4

This would update, when you copy the formula to a new sheet, because your sheet reference is relative.

1 Like
#15

I’m not referencing the current sheet, but always the previous sheet. So a new duplicate of 2025, renamed to 2026, will reference the 2026-1 which is 2025.

#16

Good idea, if sheet references are relative.

Failing with Err:501
And when duplicating 2024 to 2024_2, the field reference is still ‘2023’ and not ‘2024’ as expected.

#17

So, assuming you use Excel syntax, which you likely know by now, you would want to try ='2023'!$E$4

#18

I got things working now. I reverted the global setting from Excel A1 back to Calc A1, using in C3 the formula
=‘2023’.$E$4
for letting LO Calc doing the conversion to Excel A1, as mikekaganski explained privately on my question “when the syntax migration in LO takes place”:

when you save your file as XLSX, that file gets the formulas translated. When you open that XLSX file, Calc knows that you opened Excel file, and doesn’t convert formulas back to own style (which it could), but instead, switches to Excel syntax.

Hours-Testproject_02.4.ods (63.3 KB)
Hours-Testproject_02.4.xlsx (34.9 KB)
Both files working in LO. Both files have the Calc A1 syntax.
.xlsx Opened in Excel shows the Excel A1 syntax:
=‘2023’!$E$4

Now the problem: In .ods duplicating sheet 2025 to 2025_2 makes the referenced relative tab being 2025 as expected. The same with the .xlsx file doesn’t work in LO Calc and not in Excel, the relative tab reference stays unchanged.

#19

Ok, I got it working at least with LO Calc and the .xlsx file. The Save as .xlsx made a fixed referenced by fault (bug?):
=$‘2023’!$E$4
After repairing to
=‘2023’!$E$4
the relative tab reference gets updated on duplicate.
However in Excel it’s not updated and needs manual adaption.

And every re-open of the modified file in LO always has fixed references again and not relative tab reference.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.