Ask Your Question

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

asked 2020-04-21 17:25:14 +0200

igadget gravatar image

updated 2020-04-21 22:19:43 +0200

Using Calc in LO 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, 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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by igadget
close date 2020-04-21 22:21:28.390196

2 Answers

Sort by » oldest newest most voted

answered 2020-04-21 17:42:31 +0200

updated 2020-04-21 18:43:52 +0200

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".

edit flag offensive delete link more


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.

igadget gravatar imageigadget ( 2020-04-21 22:17:59 +0200 )edit

answered 2020-04-21 17:41:29 +0200

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)
edit flag offensive delete link more


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

igadget gravatar imageigadget ( 2020-04-21 22:23:44 +0200 )edit

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...

Mike Kaganski gravatar imageMike Kaganski ( 2020-04-21 22:52:49 +0200 )edit

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.

Lupp gravatar imageLupp ( 2020-04-21 23:34:01 +0200 )edit

Question Tools

1 follower


Asked: 2020-04-21 17:25:14 +0200

Seen: 78 times

Last updated: Apr 21 '20