Created attachment 113101 [details] bugfile Maybe duplicate of bug 69569, but don't close as dup,let Winfried decide. Steps to reproduce: - open attached file See first line - year 2004, which is a leap year, is in yearfrac calculated as non-leap year in Calc. Number of days between is 30 in both spreadsheets. Excel calculates 30/366, Calc calculates 30/365. openFormula definition here: http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018218_715980110 lead to 4.11.7 http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#Basis Actual/actual lead to http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#Basis-ProcedureE and this is probably how the code is written? should take point-> 8. Otherwise, if A and is-leap-year(year(date1)) then return 366 4.11.7.7Procedure E 1.Evaluate A: year(date1) != year(date2) 2004 != 2004 FALSE 2.Evaluate B: year(date2)!=year(date1)+1 2004 != 2005 TRUE 3.Evaluate C: month(date1) < month(date2) 10 < 11 TRUE 4.Evaluate D: month(date1) == month(date2) 10 == 11 FALSE 5.Evaluate E: day(date1) < day(date2) 10 < 11 TRUE 6.Evaluate F: (A and B) or (A and C) or (A and D and E) ( FALSE ) or (FALSE) or (FALSE) 7.If F is true then return the average of the number of days in each year between date1 and date2, inclusive. point F is FALSE 8.Otherwise, if A and is-leap-year(year(date1)) then return 366 => should be 366 ?? 9.Otherwise, if a February 29 occurs between date1 and date2 then return 366 10. Otherwise, if date2 is a February 29, then return 366 11. Otherwise return 365
Reproducible. Win7x64 Version: 4.4.0.3 Build ID: de093506bcdc5fafd9023ee680b8c60e3e0645d7 Same values as base 3 only for leap years.
Re the calculations in row 35 and 36: date1 and date2 have the same year and that is a leap year Procedure E of ODFF1.2 applies: step 1. A is false step 2-5 don't matter as A is false step 6. F is false step 7 does not apply, F is false step 8 does not apply, A is false step 9 does not apply, February 29 does not occur between date1 and date2 step 10 does not apply, date2 is not Febraury 29 step 11 remains: 365 days in a year The result may seem incorrect, but is fully compliant with ODFF1.2. Row 39, 40 and 41: date1 and date2 don't have the same year and date1 is a leapyear Procedure E of ODFF1.2 applies: step 1. A is true step 2. B is false step 3. C is false step 4/5. D is false or E is false, so they're never both true step 6. F is false step 7 does not apply, F is false step 8 does not apply, A is true and date1 is a leapyear : 366 days The result may seem incorrect, but is fully compliant with ODFF1.2. It appears that Excel uses another definition to calculate the days of the year. My first thought was that there is a bug in Calc, now I think there is not. It is a difference in definition that cannot be solved by changing the code, only by changing one of the used standards. The only way to ensure the same results seems to make Calc's YEARFRAC function non-compliant with ODFF1.2... So, now my conclusion is NOTABUG. @Raal: do you agree?
Winfried, thank you for looking on this problem.