Issues with WORKDAY formula

Aspose.Cells Product Family
1

Hello, I think there is a bug when ASPOSE is using this formula. I have an Excel file containing a formula like this:



=WORKDAY(C3,-1,HOLIDAYS)



Where HOLIDAYS is defined in the excel document as a Range. ASPOSE specifically breaks when it tries to evaluate a date

that is inside the HOLIDAYS range (If the date to be evaluated by the WORKDAY formula is not inside the HOLIDAY range it WON’T fail). I also

tried the following options where ASPOSE will also fail evaluating:



=WORKDAY(C3,-1,H7:M7) —> Using range directly

=WORKDAY(E3,-1,{42729,42730,42736,42737,42795,42788}) —> Using a constant array



The cells in which will fail are C48, D48 and E48 which correspond to the evaluation of 25-Dec-2016 as a HOLIDAY.



The exception is thrown in the following method:



private readonly Workbook _workbook;

_workbook.CalculateFormula(false);



As: Error in calculating Cell[[0]Threshold Analysis!C48]





I attach an excel file for you to test.

2

Hi,


Thanks for your posting and using Aspose.Cells.

We were able to observe this issue and logged it in our database for a fix.

This issue has been logged as

  • CELLSNET-45190 - Exception occurred while calculating WORKDAY formula

C#
Workbook wb = new Workbook(“ASPOSE_Template.xlsx”);
wb.CalculateFormula();

Exception
Aspose.Cells.CellsException was unhandled
HResult=-2146232832
Message=Error in calculating Cell[[0]Threshold Analysis!C48]
Source=Aspose.Cells
StackTrace:
at . ( )
at ​ . ​ (CalculationOptions )
at ​ . ​ (Boolean , ICustomFunction )
at Aspose.Cells.Workbook.CalculateFormula()
at CELLSNET.Program.f1() in E:\Downloads\Misc\Aspose\Projects\AsposeSol\CELLSNET\Program.cs:line 211
at CELLSNET.Program.Main(String[] args) in E:\Downloads\Misc\Aspose\Projects\AsposeSol\CELLSNET\Program.cs:line 33
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
HResult=-2146233080
Message=Index was outside the bounds of the array.
Source=Aspose.Cells
StackTrace:
at . (Int32 , Int32 , Int32[] , , Int32 )
at . ( [] , )
at . ( )
at . ​ ( )
at . ​ ( )
at . ​ ​ ( , )
at . ( ​ , )
InnerException:

3
Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-45190 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
4

Hi,


Please try our latest fix/version: Aspose.Cells for .NET v17.2.8:

Aspose.Cells for .NET v17.02.8 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.02.8 (.NET 4.0) compiled in .NET Framework 4.0.

Your issue “CELLSNET-45190” should be fixed in it.

Let us know your feedback.

Thank you
5

Hello I tried v17.02.8 (.NET 4.0) and it is not working, I sued the previous attached document, I am having the following error:

Error in calculating Cell[[0]Threshold Analysis!C53]

Not sure about the reason as that cell does not include any day within HOLIDAYS range.

Regards.

6

Hi,


I tested your scenario/ case again with v17.2.8 using your original file with the following sample code, it works fine and and I could not find the issue (no error occurred):
e.g
Sample code:

Workbook wb = new Workbook(“e:\test2\ASPOSE_Template.xlsx”);
wb.CalculateFormula();

Please make sure that you are using latest version/fix: Aspose.Cells for .NET v17.2.8. You may print the version number at runtime to get to know you are using the right version:
e.g
Sample code:

Console.WriteLine(CellsHelper.GetVersion());


If you still find the issue, kindly do create a separate console application with v17.2.8, zip the project and post us there to reproduce the issue on our end, we will check it soon.

Thank you.
7

The issues you have found earlier (filed as CELLSNET-45190) have been fixed in Aspose.Cells for .NET 17.3.0.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.