Free Support Forum - aspose.com

Subtraction of DateTime in a Cell formula

Hello,


As an example I need to pass the following string to the formula accessor of the Aspose.Cells.Cell object:

"“12 January 2010 18:30:00” -"12 January 2010 18:15:00"“

When I try this the DoubleValue accessor of the Cell throws an exception. I’ve tested this string format in Microsoft Excel, which handles it fine. I’ve also tested the following:

”“12 January 2010” -"12 January 2010""

which works fine on the Cell object, however I need the time component factored in as well.

Can you please tell me the format of the string I need to pass?

David

Hi David,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

We have found your mentioned issue after an initial test. We will look into it and get back to you soon. Your issue has been registered in our internal issue tracking system with issue id CELLSNET-13474.

Thank you & Best Regards,

Hi,

Please try the attached version.

We have fixed the issue.

Thank you.

Hello,


Thank you for the quick update. The fix you provided fixes the issue for a date with a time that includes seconds, like the following:

"“12 January 2010 18:30:05” -"12 January 2010 18:15:10"“

However, if there were no seconds then the string representation of the Date time will be as follows and the same exception is being thrown:

”“12 January 2010 18:30” -“12 January 2010 18:15"“

Furthermore, if the seconds or minutes are provided but are zero then the same exception is thrown:

”“12 January 2010 18:30:00” -“12 January 2010 18:15:00"”

”“12 January 2010 18:00” -"12 January 2010 18:05""

Finally, if the calculation has a valid hour, second and/or minute component then the DoubleValue returned for the cell appears to always be 8.0.

David

Hi David,

Well, I tested your scenario with v4.8.2.1 but could not find any issue. It works as MS Excel.

Here is my sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

Aspose.Cells.Cell cell1 = worksheet.Cells[“A2”];
cell1.Formula = "=“12 January 2010 18:30:00”-“12 January 2010 18:15:00"”;
Aspose.Cells.Cell cell2 = worksheet.Cells[“A3”];
cell2.Formula = "=“12 January 2010 18:30”-“12 January 2010 18:15"”;
Aspose.Cells.Cell cell3 = worksheet.Cells[“A4”];
cell3.Formula = "=“12 January 2010 18:00”-“12 January 2010 18:05"”;

workbook.CalculateFormula();

MessageBox.Show(cell1.DoubleValue.ToString());
MessageBox.Show(cell2.DoubleValue.ToString());
MessageBox.Show(cell3.DoubleValue.ToString());

workbook.Save(“e:\test\jj22sdfsdfd11out_hyperlink.xls”);


Could you create a sample code/application to post it here to show the issue, we will check it soon.

Thank you.

Disregard my last email, the fix works perfectly :slight_smile:


Thank you very much.

David

The issues you have found earlier (filed as 13474) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.