[solved] Calc bug when using TEXT to display month in longer formula?

[igadget]

Using Calc in LO 6.4.2.2 on Ubuntu 18.04, but also on OnlyOffice 5.4.2 shipping with Nextcloud 18.0.3, I’m experiencing this:

Cell A1 contains a date, i.e. 03-02-20. Cell is formatted as date as well.

Formula in cell A2:

="Week "&(WEEKNUM(A1;21))&" ("&TEXT(A1;"DD")&" - "&TEXT((A1+6);"DD"&" "&TEXT(A1;"MMMM")&")")

Expected result:

Week 6 (3 - 9 february)

Actual result (LO 6.4.2.2, set to Dutch):

Week 6 (3 - 9 f20b20200a2020i)

Actual result (OnlyOffice 5.4.2):

Week 6 (3 - 9 fruri)

Can someone please try the formula above and confirm this bug?

[mauricio]

Your function:

="Week "&(WEEKNUM(A1;21))&" ("&TEXT(A1;"DD")&" - "&TEXT((A1+6);"DD"&" "&TEXT(A1;"MMMM")&")")

Week 6 (3 - 9 f20b20200a2020i)

Function correctly

="Week "&(WEEKNUM(A2,21))&" ("&TEXT(A2,"DD")&" - "&TEXT((A2+6),"DD") &" "&TEXT(A2,"MMMM")&")"

Week 6 (03 - 09 February)

[igadget]

Thanks Mauricio! Looking at your answer, I see you’re using comma instead of ‘;’. Is that on purpose? If so, why?

[mikekaganski]

That’s because unfortunately, different locales have different defaults for separators (including function arguments’), function names etc. All that creates a variety of incompatible syntaxes inside the single application. The semicolon is universal and recognized always, while comma is used e.g. in en-US locale, and can’t be used in those locales that don’t use it. Copying the formula from formula bar gives the locale defaults…

[Lupp]

Concerning the comma-semicolon-mess and how it evolved:
A rather complete explanation containing a kind of workaround and a plea for specific option settings you find here.

[mikekaganski]

Check the parentheses in your last part:

TEXT((A1+6);"DD"&" "&TEXT(A1;"MMMM")&")")

Given that your expected string in TEXT(A1;“MMMM”) in your locale is “february”, the format string that is passed to the first TEXT becomes “DD february)”. Some characters in it are treated as is, and some as format specifiers resulting in year appearing in the string in places of “e” and “r”.

[igadget]

Thanks! I’m so glad I did not file this as a bug directly. Looking at it now it’s so obvious I can’t believe I missed it.