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
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
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".
(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.
haven't tested the code yet, but possible souce code pointer: https://opengrok.libreoffice.org/xref/core/scaddins/source/datefunc/datefunc.cxx?r=df1fb2f7#534 https://opengrok.libreoffice.org/xref/core/scaddins/source/datefunc/datefunc.cxx?r=df1fb2f7#45 https://opengrok.libreoffice.org/xref/core/scaddins/source/datefunc/deffuncname.hxx?r=df0c18b4#32
https://opengrok.libreoffice.org/xref/core/scaddins/source/pricing/pricing.cxx?r=df1fb2f7#108
sorry comment 5 was wrong https://opengrok.libreoffice.org/xref/core/scaddins/source/datefunc/datefunc.cxx?r=df1fb2f7#103
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
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
From my perspective not a bug
Hi Eike, I thought you might be interested in this issue
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.
(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
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