Inconsitent Formula Calcluation

Aspose.com » Public Forums for Customers an... » Aspose.Cells Product Family » Inconsitent Formula Calcluation

Last post 05-25-2007, 8:39 AM by rohitob. 4 replies.


	Sort Posts: Previous Next

05-21-2007, 9:08 AM 77375

rohitob
Joined on 03-28-2007
Posts 81

Inconsitent Formula Calcluation

Attachment: Present (inaccessible)

Hi Aspose Team,

I am using the java version of Aspose Cell (for exact version please refer to last version posted at http://www.aspose.com/Community/Forums/thread/74833.aspx).

I am using the combination of MID,LEFT & RIGHT functions to parse a non-standard date into standard US date format. The resultant output is inconsistent. I perform the following steps
1) I open a template file (having formulas)

2) I copy some data into 1st worksheet.

3) Run calculate formula on template sheet

4) Save 2nd worksheet as a new excel/csv file.

I have attcahed for you reference the sample input,template & output file . Please advice.

Thxs,

05-21-2007, 11:07 PM 77510 in reply to 77375

Warren
Joined on 08-02-2004
Posts 1,406

Re: Inconsitent Formula Calcluation

Attachment: Present (inaccessible)

Hi,

The result of the formula which combins of MID,LEFT & RIGHT functions is a string value not a date value. So the style of the cell does not effect.

And it seems you should change your formula to =IF(ISBLANK(INPUT!$A2),"",DATEVALUE(""&CONCATENATE(MID(DateConversion!A2,5,2),"/",RIGHT(DateConversion!A2,2),"/",LEFT(DateConversion!A2,4))))

Please try this fix. We support to parse this date format.

Warren Zhang
Developer
Aspose Nanjing Team
About Us
Contact Us

05-23-2007, 3:27 PM 77849 in reply to 77510

rohitob
Joined on 03-28-2007
Posts 81

Re: Inconsitent Formula Calcluation

Attachment: Present (inaccessible)

Hi Warren,

I tired your fix, but doesn't seem to work for all rows.

It gives me '#VALUE!' error for few rows(eg row#30). please advice. I am attaching the output file generated after using formula.

Thanks,

rohitob

05-23-2007, 9:15 PM 77872 in reply to 77849

Warren
Joined on 08-02-2004
Posts 1,406

Re: Inconsitent Formula Calcluation

Attachment: Present (inaccessible)

Hi,

Please try this fix.

Warren Zhang
Developer
Aspose Nanjing Team
About Us
Contact Us

05-25-2007, 8:39 AM 78131 in reply to 77872

rohitob
Joined on 03-28-2007
Posts 81

Re: Inconsitent Formula Calcluation

Thanks the fix worked.