DATEVALUE Forumla Evaluation

Aspose.com » Public Forums for Customers an... » Aspose.Cells Product Family » DATEVALUE Forumla Evaluation

Last post 08-14-2007, 8:49 PM by Warren. 27 replies.

Page 1 of 2 (28 items) 1 2 Next >
	Sort Posts: Previous Next

05-07-2007, 5:38 PM 75600

rohitob
Joined on 03-28-2007
Posts 81

DATEVALUE Forumla Evaluation

Hi Aspose Team,

I am using DATEVALUE function to convert a date_text(21-APR-2003) into a standard date.But after calculation it gives me '#VALUE' error. Please advice.

Thanks,

rohitob

05-07-2007, 8:44 PM 75613 in reply to 75600

Laurence
Joined on 05-28-2003
Posts 7,779

Re: DATEVALUE Forumla Evaluation

Hi Rohitob,

I try the following code and it works fine:

Workbook wb = new Workbook();
   wb.Worksheets[0].Cells["A1"].Formula = "=DateValue(\"21-APR-2003\")";
   wb.CalculateFormula();
   Console.WriteLine(wb.Worksheets[0].Cells["A1"].Value);

Which version of Aspose.Cells are you using?

Laurence Chen
Chief Architect
Aspose Nanjing Team
About Us
Contact Us

05-07-2007, 11:31 PM 75622 in reply to 75600

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

Re: DATEVALUE Forumla Evaluation

Hi,

Please try the fix in http://www.aspose.com/COMMUNITY/Forums/thread/74833.aspx.

Warren Zhang
Developer
Aspose Nanjing Team
About Us
Contact Us

07-05-2007, 12:51 PM 82860 in reply to 75622

rohitob
Joined on 03-28-2007
Posts 81

Re: DATEVALUE Forumla Evaluation

Attachment: Present (inaccessible)

Hi Warren,

I found another instance, when the DATEVALUE function gives error. I have attached the sample input file I am trying to parse & when I run DATEVALUE function (=DATEVALUE(C9)))for columns C &D it gives me VALUE error.

Thanks,

Rohit Oberoi

07-05-2007, 2:38 PM 82869 in reply to 82860

Amjad Sahi
Joined on 08-30-2006
Posts 17,640

Re: DATEVALUE Forumla Evaluation

Hi Rohit,

Thanks for pointing it out.

We tested it a bit and produced the error "#VALUE!".

We will resolve it soon.

Thank you.

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

07-05-2007, 9:06 PM 82895 in reply to 82860

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

Re: DATEVALUE Forumla Evaluation

Attachment: Present (inaccessible)

Hi,

Please try this fix.

Warren Zhang
Developer
Aspose Nanjing Team
About Us
Contact Us

07-06-2007, 7:37 AM 82980 in reply to 82895

rohitob
Joined on 03-28-2007
Posts 81

Re: DATEVALUE Forumla Evaluation

Thxs Warren,

This fixes the issue.

Thanks,

Rohit

07-06-2007, 9:43 AM 82995 in reply to 82980

rohitob
Joined on 03-28-2007
Posts 81

Re: DATEVALUE Forumla Evaluation

Attachment: Present (inaccessible)

Hello Aspose Team,

Found another file where the DATEVALUE failed to evaluate. Please let me know.

Thanks,

Rohit

07-06-2007, 12:31 PM 83027 in reply to 82995

Amjad Sahi
Joined on 08-30-2006
Posts 17,640

Re: DATEVALUE Forumla Evaluation

Hi Rohit,

No, I don't think so that if there is any problem. The DATEVALUE() function demands that you will enter data as text value parameter. And if your are referencing a cell as its parameter, it should be in the general text format. I checked your file, "I12" and "I13" cells are formatted as Date, So if you use e.g., =DATEVALUE(I12) it will give #VALUE! output. You may confirm in MS Excel also.

Thank you.

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

07-06-2007, 12:39 PM 83028 in reply to 83027

rohitob
Joined on 03-28-2007
Posts 81

Re: DATEVALUE Forumla Evaluation

Hi Amjad,

Sorry for not being specific.. the problem is in cells "H12" and "H13" and not "I12" and "I13". The same formula works fine in excel.

Thanks,

Rohit

07-06-2007, 1:31 PM 83032 in reply to 83028

Amjad Sahi
Joined on 08-30-2006
Posts 17,640

Re: DATEVALUE Forumla Evaluation

Attachment: Present (inaccessible)

Hi Rohit,

Well, it 's working. I tested it.

Following is my sample code using your template file and attached is the screen shot of the output value.

import java.io.*;
import com.aspose.cells.*;

class DateVal1
{

   public static void main(String []args)
   {

           try
         {

         Workbook workbook = new Workbook();
               workbook.open("e:\\Files\\TEST_FILE.xls");
               workbook.calculateFormula();
         Worksheet worksheet = workbook.getWorksheets().getSheet(0);
         Cells cells = worksheet.getCells();
         Cell cell = cells.getCell("A22");
         cell.setFormula("=DATEVALUE(H12)");
         workbook.calculateFormula();
         System.out.println(cell.getValue());

         }

         catch(Exception ee)
         {

         System.out.println(ee);
         }

}

}

Please make sure that you are using the latest hotfix which Warren posted you.

Thank you.

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

07-06-2007, 2:01 PM 83035 in reply to 83032

rohitob
Joined on 03-28-2007
Posts 81

Re: DATEVALUE Forumla Evaluation

Attachment: Present (inaccessible)

Hi Amjad,

Here's my sample of code..I have attached the reqd sample input & template files. After you run the beloe code..check the column "F" of dest.csv

~~~~~~~~~~~~~~~~~~

Workbook workbook = new Workbook();

workbook.open("C:\\TEST_FILE.xls");

Worksheet inputWorksheet = workbook.getWorksheets().getSheet(0);

Workbook dest = new Workbook();

dest.open("C:\\TEST_Template.xls",FileFormatType.DEFAULT);

Worksheet destWorksheet = dest.getWorksheets().getSheet(0);

destWorksheet.copy(inputWorksheet);

dest.calculateFormula();

dest.getWorksheets().setActiveSheet(1);

dest.save("C:\\dest.csv", FileFormatType.CSV);

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

07-07-2007, 1:30 AM 83063 in reply to 83035

Amjad Sahi
Joined on 08-30-2006
Posts 17,640

Re: DATEVALUE Forumla Evaluation

Hi,

Thanks for the template files,

We tested a bit and found the problem, We will figure out your issue soon.

Thank you.

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

07-08-2007, 7:40 PM 83112 in reply to 83063

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

Re: DATEVALUE Forumla Evaluation

Attachment: Present (inaccessible)

Hi,

Please try this fix.

Warren Zhang
Developer
Aspose Nanjing Team
About Us
Contact Us

07-09-2007, 9:28 AM 83188 in reply to 83112

rohitob
Joined on 03-28-2007
Posts 81

Re: DATEVALUE Forumla Evaluation

Attachment: Present (inaccessible)

Thxsa Warren,

The fix works fine for the given set of input & template file. I have another set of input & template files where the DATEVALUE function failed again. Please look for column "AU" in the dest.csv using the same code above.Any reason for incosistence behaviour?

Thanks,

Rohit

Page 1 of 2 (28 items) 1 2 Next >