YEARFRAC Issue with 29th of Feb and 1st of Jan

Aspose.Cells Product Family
1

Hi,

We seem to have a problem with YEARFRAC as it pertains to the 29th of Feb when determining the difference with the 1st of Jan.

I’m not sure if it’s a problem across the board but the attached workbook demonstrates the issue.

All you need to do to replicate the issue is to open and calculate the workbook. This is with v24.6.0

var filePath = @"C:\Users\<user>\Downloads\T4_YEARFRAC.xlsx";
var inputStream = new MemoryStream(File.ReadAllBytes(filePath));

using (var workbook = new Workbook(inputStream))
{
    workbook.CalculateFormula();
}

This is the formula …

=IF(NOT(Calculate),"",
LET(
  _fiveYearDate, EDATE([@Date],-60),
  _maxDate, MAX(_fiveYearDate,MIN([Date])),
  _yearsSinceDateOrStartService, YEARFRAC(_maxDate,[@Date]+1,1),
_yearsSinceDateOrStartService))

… and at that point, _maxDate is equal to the 29th of Feb, 2016 and [@Date]+1 is the 1st of Jan, 2017 … so something with those dates isn’t playing nicely together.

This is the error I am getting …

Exception: Aspose.Cells.CellsException: ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime.(Based on cell LSLCalculation!B310)

T4_YEARFRAC.zip (60.7 KB)

2

UPDATE, I can simplify this for you, create a new workbook, put this formula in cell A1, run the code I provide and it will fail …

=YEARFRAC("29/02/2016", "01/01/2017", 1)

That formula alone will recreate the issue so you guys can work out why it is happening.

3

@braddixonorigin,

Thanks for the template XLSX file and details.

After an initial test, I am able to reproduce the issue as you mentioned by using your template XLSX file and sample code. I found YEARFRAC formula issue with 29th of Feb and 1st of Jan.

We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-56105

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

4

Can I ask, we now have paid support so can we discuss the prioritisation of this issue? I should point out though, the support is under a different user so I assume you’d need validation re: the licence and agreement.

5

@braddixonorigin
Thank you for your feedback. Please login with your Priority Support account on the priority support helpdesk and log a ticket/thread (refer to the existing ticket here) there.

7

@Paul.Collett.originenergy.com.au
Please login with your Priority Support account on the priority support helpdesk and log a ticket/thread (refer to the existing ticket here) there.

8

Done, thanks!

9

@Paul.Collett.originenergy.com.au
You are welcome.

10

@Paul.Collett.originenergy.com.au,

We are pleased to inform you that your issue (Ticket ID: “CELLSNET-56105”) has been resolved on a priority basis. You may consult with the paid support lead in your paid support thread to obtain the fix. By default, the fix will be included in our upcoming release (Aspose.Cells v24.8), which we plan to release in the second week of August 2024.

11

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