Aspose Cells.NET uses different DayCount Convention in the YearFrac Function than Excel

Aspose.Cells Product Family
1

Hi, the Aspose.Cells YearFrac(startDate,endDate,convention) Function yields different Results than Excel. See the attached Excel Document where a Series of Dates is given in Column A and the YearFrac-Formula in Column B. The Dates always happen at December 12 from 2019-12-17 to 2029-12-17. The Convention is 1 (Actual-Actual) and Excel yields straight 1.00 as Year Fractions for each year, which corresponds to the Actual-Actua-ICMA Convention described here
Aspose unfortunately yields Values between 0.999887716 and 1.000112284 due to leap-year Variance, which rather corresponds to the unmodified Actual-Actual Convention.
Is there a way to modify this behavior or align Aspose Output to Excel Output?
CompareYearFraction.zip (9.4 KB)

2

@mheuer,

Thanks for the template file.

I reproduced the issue as you mentioned by using the following sample code with your template file. I found Aspose.Cells uses different DayCount convention in the YEARFRAC function than MS Excel.
e.g.
Sample code:

var workbook = new Workbook("e:\\test2\\CompareYearFraction.xlsx");
workbook.CalculateFormula();
workbook.Save("e:\\test2\\out1.xlsx", SaveFormat.Xlsx);

In MS Excel, I set Calculation Options to Manual and then open the output file into it. I found the different values as you mentioned. I have logged a ticket with an id “CELLSNET-52270” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

3

Thank you. Can you please notify me via EMail, since I won’t watch this channel every day?

4

@mheuer,

When we post a reply here in this thread, you will also get automatic notification email @ your designated email id (you subscribed for your account id (mheuer)), so you may keep any eye on your email inbox if you do not want to visit this thread directly.

5

@mheuer,

This is to inform you that your issue has been resolved now. The fix will be included in our upcoming release (Aspose.Cells 22.12) which is scheduled in the first half of December, 2022. You will also be notified when the next version is released.

6

The issues you have found earlier (filed as CELLSNET-52270) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi