[Libreoffice-bugs] [Bug 139510] CALC MONTH(TODAY)) producing incorrect Descriptor which is exacerbated by adding the value of a reference cell

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=139510

Mike Kaganski <mikekaganski at hotmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |NOTABUG
             Status|UNCONFIRMED                 |RESOLVED

--- Comment #2 from Mike Kaganski <mikekaganski at hotmail.com> ---
The function MONTH() returns a number (as an integer between 1 and 12) of the
month in the passed date [1]. It does *not* return a date, which in Calc is a
number of days since Epoch [2].

On the other hand, formatting a cell as Date makes Calc treat the value in the
cell as a date. So the integer between 1 and 12 is considered number of days
since Epoch, which by default is since 1899-12-30. For current date (January
2021), MONTH(TODAY()) returns 1, which, when considered a date, is treated as
"1 day after 1899-30-12", which is 1899-12-31. Then that date's month is shown,
which is December.

If you add some number to the MONTH() result, it produces respectively larger
number: e.g., adding 1, you get sum of 2, which, treated as "number of days
since 1899-12-30", gives you 1900-01-01, which was in January.

Naturally, trying to treat month number as number of days since some point is
very unlikely to be what you want. It is user error to try to format random
numbers as dates. So this is not a bug.

If you need a string showing the month, and also a number for formula
processing, you may either use two cells (e.g., B1 for "=TODAY()" formatted as
date "MMM", and C1 for "=MONTH(B1)"); or you may use "MONTH($B$1)" directly in
cells where you used "C1"; or you may keep the cell content as is now, but
instead of applying "date" format to C1, use conditional format to make the
cell display needed text for different numbers. An example of the latter
suggestion would be four formats, like "if cell value is between 1 and 3, apply
number format '[=1]"Jan";[=2]"Feb";"Mar"', else if cell value is between 4 and
6, apply number format '[=4]"Apr";[=5]"May";"Jun"', else if cell value is
between 7 and 9, apply number format '[=7]"Jul";[=8]"Aug";"Sep"', else apply
number format '[=10]"Oct";[=11]"Nov";"Dec"'". This is bulky, and I would advise
to use two cells.

[1] https://help.libreoffice.org/7.1/en-US/text/scalc/01/func_month.html
[2]
https://help.libreoffice.org/7.1/en-US/text/shared/optionen/01060500.html#hd_id3147125

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210109/b9cea25a/attachment.htm>