Using Time Functions in Formula Cells

Aspose.Cells Product Family
1

Hi,

I’m using the following code to calculate time valus in excel:

var styleTime = new Aspose.Cells.Style {Number = 21};
resultSheet.Cells[count, 6].SetStyle(styleTime);
resultSheet.Cells[count, 6].Formula = “=I1+ZEIT(0,0,E3*60)”;

The results cells (column G,H) are all marked as erroneous. When I click on each cell separately, all formulas are evaluated correctly. Recalculating the whole worksheet does not help.

What is here wrong?

Thanks in advance !

The attachments: Template and results ase xlsx

2

Hi,


Well, I have evaluated your issue, here is my code with some comments:

Sample code:

Workbook workbook = new Workbook(“e:\test2\results.xlsx”);
var styleTime = new Aspose.Cells.Style { Number = 21 };
Worksheet resultSheet = workbook.Worksheets[1];
int count = 2;
resultSheet.Cells[count, 6].SetStyle(styleTime);
//It will not work and give #NAME? error as ZEIT is not in Enligsh (US) function
///resultSheet.Cells[count, 6].Formula = “=I1+ZEIT(0,0,E360)";
//It will work
resultSheet.Cells[count, 6].Formula = "=I1+TIME(0,0,E360)”;

workbook.CalculateFormula();

workbook.Save(“e:\test2\outResults3.xlsx”);

While setting formulas please use English names i.e. TIME instead of ZEIT. It works fine.
I have attached my output file, I used your file as input/template file.

See the topic for reference:
http://www.aspose.com/docs/pages/viewpage.action?pageId=15565448

Note: I am using latest fix i.e. v7.3.5.1, we also recommend you to download and try the latest fix/version: Aspose.Cells for .NET v7.3.5.1


Let us know if you still have any confusion or issue.

Thank you.

3

Thanks - works great !

4

Hi,


Good to know that your issue is sorted out.

Have a good day!

Thank you.