Bug 138313 - MONTHS() function gives the wrong result
Summary: MONTHS() function gives the wrong result
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.6.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2020-11-18 17:41 UTC by guy.roydor
Modified: 2020-12-03 16:01 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file with the two MONTHS() formulas, in English locale (8.64 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-11-19 16:08 UTC, Ming Hua
Details

Note You need to log in before you can comment on or make changes to this bug.
Description guy.roydor 2020-11-18 17:41:39 UTC
Description:
Le troisième argument de la fonction "NB.MOIS" est inopérant. Les valeurs 1 ou 2 donnent le même résultat

Actual Results:
NB.MOIS(12/12/2010;12/12/2012;0)=24 
NB.MOIS(12/12/2010;12/12/2012;1)=24 

Expected Results:
NB.MOIS(12/12/2010;12/12/2012;0)=24 
NB.MOIS(12/12/2010;12/12/2012;1)=12


Reproducible: Always


User Profile Reset: No



Additional Info:
RAS
Comment 1 Ming Hua 2020-11-19 16:08:18 UTC
Created attachment 167399 [details]
Sample file with the two MONTHS() formulas, in English locale

NB.MOIS is the French translation for function MONTHS().

I can reproduce the reported behavior that both formulas returning 24, with both 6.4.7 and 7.1.0 alpha1+ on Windows 10.  I've also attached a sample file with the two formulas.  I don't have access to Excel to test the other part.

Our help documentation [1] only describes the third argument of MONTHS() vaguely: "Type calculates the type of difference. Possible values include 0 (interval) and 1 (in calendar months)."

So not sure if we mean the same thing with this Type argument as Excel does.  And if we don't, we probably should.  The current description "in calendar months" seems to agree with the result 24 though.

1. https://help.libreoffice.org/latest/en-US/text/scalc/01/04060111.html?DbPAR=CALC#bm_id3152898
Comment 2 Justin L 2020-12-01 06:47:24 UTC
Excel 2016 doesn't have the function "months", and I don't see any reference to it on the Internet. It looks like a LO only function.

I certainly would not expect 12 months as a response to "how many months are between Dec 2010 and Dec 2012". On the other hand, I can't imagine what that third value could refer to except perhaps inclusive/exclusive of the days -  to perhaps swing the value to 23 or 25 in the case of "interval".
Comment 3 Ming Hua 2020-12-01 07:32:03 UTC
(In reply to Justin L from comment #2)
> Excel 2016 doesn't have the function "months", and I don't see any reference
> to it on the Internet. It looks like a LO only function.
Yeah, the "different from Excel" part was my baseless speculation, sorry about that.
Comment 5 himajin100000 2020-12-01 09:05:15 UTC Comment hidden (obsolete)
Comment 7 himajin100000 2020-12-01 09:16:18 UTC
In my environment, the following formula returns 24

=MONTHS(DATE(2010,12,12),DATE(2012,12,12),1)

---
Version: 7.2.0.0.alpha0+ (x64)
Build ID: 4e63ec27b69fa01ff610c894c9fbf05c377a6179
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: default; VCL: win
Locale: ja-JP (ja_JP); UI: en-US
Calc: CL
Comment 8 [REDACTED] 2020-12-01 11:14:56 UTC
The meaning of third argument may be clear from this example. From my point of view it is about *full months* and a *counting diff of calendeder month months* (in fact make information about day obsolete)

Date 1	        Date 2	        arg=0	arg=1
2010-12-12	2012-12-01	23	24
2010-12-12	2012-12-02	23	24
2010-12-12	2012-12-03	23	24
2010-12-12	2012-12-04	23	24
2010-12-12	2012-12-05	23	24
2010-12-12	2012-12-06	23	24
2010-12-12	2012-12-07	23	24
2010-12-12	2012-12-08	23	24
2010-12-12	2012-12-09	23	24
2010-12-12	2012-12-10	23	24
2010-12-12	2012-12-11	23	24
2010-12-12	2012-12-12	24	24
2010-12-12	2012-12-13	24	24
2010-12-12	2012-12-14	24	24
2010-12-12	2012-12-15	24	24
2010-12-12	2012-12-16	24	24
2010-12-12	2012-12-17	24	24
2010-12-12	2012-12-18	24	24
2010-12-12	2012-12-19	24	24
2010-12-12	2012-12-20	24	24
2010-12-12	2012-12-21	24	24
2010-12-12	2012-12-22	24	24
2010-12-12	2012-12-23	24	24
2010-12-12	2012-12-24	24	24
2010-12-12	2012-12-25	24	24
2010-12-12	2012-12-26	24	24
2010-12-12	2012-12-27	24	24
2010-12-12	2012-12-28	24	24
2010-12-12	2012-12-29	24	24
Comment 9 [REDACTED] 2020-12-01 11:22:59 UTC
From my perspective not a bug
Comment 10 Xisco Faulí 2020-12-01 11:44:13 UTC
Hi Eike, I thought you might be interested in this issue
Comment 11 Eike Rathke 2020-12-01 13:53:13 UTC
As Uwe said and lined out with his example.

Type/mode 0 returns the interval in full months.
Type/mode 1 returns the difference in number of calendar months (as if you are looking at a paper calendar and count number of paper sheets seen).

And no, all functions implemented in the scaddins/source/datefunc/ Add-In didn't exist in Excel back then (and don't today), they are also not included in the ODF OpenFormula (ODFF) standard.

Not a bug.
Comment 12 Eike Rathke 2020-12-01 13:56:45 UTC
(In reply to Eike Rathke from comment #11)
> all functions implemented in the scaddins/source/datefunc/ Add-In
Which btw are those of
https://help.libreoffice.org/latest/en-GB/text/scalc/01/04060111.html
Comment 13 guy.roydor 2020-12-03 16:01:01 UTC
Assistant de la fonction NB.MOIS indique pour le troisième argument :
Mode (requis) :
type de calcul : Mode=0 pour le nombre de mois dans l'intervalle
Mode=1 pour le mois du calendrier

pour le moins incompréhensible

traduction via google
Assistant of the NB.MOIS function indicates for the third argument:
Mode (required):
type of calculation: Mode = 0 for the number of months in the interval
Mode = 1 for the calendar month

to say the least incomprehensible