Bug 146235 - Wrong value from TEXT function in CALC on calculated dates (Rel. Versione: 6.4.7.2 Build ID: 1:6.4.7-0ubuntu0.20.04.2 Thread CPU: 4; SO: Linux 5.4; Resa interfaccia: predefinito; VCL: gtk3; Versione locale: it-IT (it_IT.UTF-8); Lingua interfaccia: it-IT)
Summary: Wrong value from TEXT function in CALC on calculated dates (Rel. Versione: 6....
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.7.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-12-15 08:31 UTC by Vito Caleandro
Modified: 2021-12-15 09:06 UTC (History)
0 users

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Vito Caleandro 2021-12-15 08:31:48 UTC 
Description:
The TEXT function appears to give a wrong result if used on a calculated date.


Steps to Reproduce:
Example:
1) Set A1 to 31/01/2022
2) Set B1 to =A1+1
3) Set C1 to =TEXT(MONTH(B1);"MMMM") (nella versione italiana è =TESTO(MESE(B1);"MMMM")


Actual Results:
result displayed is january (nella versione italiana è gennaio)

Expected Results:
instead of february (nella versione italiana deve essere febbraio)


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
the MONTH function applied to B1 cell  gives the correct result (2)

Versione: 6.4.7.2
Build ID: 1:6.4.7-0ubuntu0.20.04.2
Thread CPU: 4; SO: Linux 5.4; Resa interfaccia: predefinito; VCL: gtk3; 
Versione locale: it-IT (it_IT.UTF-8); Lingua interfaccia: it-IT
Calc: threaded
Comment 1 Mike Kaganski 2021-12-15 09:06:00 UTC 
This is not a bug.

MONTH [1] returns an integer between 1 and 12, that indicates a month number. So the return of MONTH is *not* a date.

Format string "MMMM" [2] is a Date format, which means that the value passed to TEXT is a *date*. So the number "2" obtained from MONTH("2022-02-01") goes to TEXT and treated as a serial date (i.e., number of days since epoch - typically 1899-12-30). The 2nd day since then is 1900-01-01, which was on January, which you correctly get.

The correct way of using TEXT with "MMMM" is:

3) Set C1 to =TEXT(B1;"MMMM")

[1] https://help.libreoffice.org/7.2/en-US/text/scalc/01/func_month.html?DbPAR=CALC#bm_id3149936
[2] https://help.libreoffice.org/7.2/en-US/text/shared/01/05020301.html?&DbPAR=CALC&System=WIN