Issue for TEXT function

Aspose.Cells Product Family
1

Hi,

Excel will return “30-Jun” for both =TEXT(“30 Jun”,“dd-mmm”) and =TEXT(“Jun 30”, “dd-mmm”), while Aspose Cells Java will return “01-Jun”.

Thanks,
Hui

2

@huichen,
Please share your sample file and code snippet with us for our testing. We will reproduce the problem and provide our feedback after analysis.

3

@huichen,

I did evaluate your issue further using a simple template file (attached), it works fine and same as per MS Excel.

This is not true as I manually tested in Ms Excel. Ms Excel returns “30-Jun” for “=TEXT(“30 Jun”,“dd-mmm”)”. While it returns “01-Jun” for “=TEXT(“Jun 30”, “dd-mmm”)”. See the screenshot for your reference:

I evaluated your scenario/ case a bit using the following sample code with a simple template file (attached), it works fine and as expected, same as Ms Excel:
e.g
Sample code:

Workbook workbook = new Workbook("f:\\files\\Bk_textfunc1.xlsx");
		System.out.println(workbook.getWorksheets().get(0).getCells().get("A1").getStringValue());
		System.out.println(workbook.getWorksheets().get(0).getCells().get("B1").getStringValue());
		workbook.calculateFormula();
		System.out.println(workbook.getWorksheets().get(0).getCells().get("A1").getStringValue());//30-Jun - Ok
		System.out.println(workbook.getWorksheets().get(0).getCells().get("B1").getStringValue());//01-Jun - Ok

files1.zip (5.4 KB)

PS. I am using our latest version/fix: Aspose.Cells for Java v19.4.x (please try it if you are not already using it).

4

Hi,
Thanks for the update. Below is my test code,
Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
System.out.println(ws.calculateFormula("=TEXT(“30 Jun”,“dd-mmm”)"));
System.out.println(ws.calculateFormula("=TEXT(“Jun 30”,“dd-mmm”)"));

Also, I have tested Excel 2007 and Excel 2010 and it returns “30-Jun” for both.

Could you please let me know which Excel version you are using?

Thanks,
Hui

5

@huichen,

I tested it in MS Excel 2007, 2010 and 2013, all return “30-Jun” for “=TEXT(“30 Jun”,“dd-mmm”)” and all return “01-Jun” for “=TEXT(“Jun 30”, “dd-mmm”)”.

Here is my env. details.
OS: Windows8 (on c (physical hard disk) drive as primary os), regional/locale settings: english (us) settings, etc.

What is your environment details?

6

Hi,
I Have tested on at least 4 machines and they all returns “30-Jun”,

  1. win7(64) + Excel 2007
  2. win7(64) + Excel 2010
  3. win7(64) + Excel 2016
  4. win10(64) + Excel 2016

The locale is english(US).

It’s weired, Could you please test on some more machinces?

Thanks,
Hui

7

@huichen,
I have tested the scenario on another system and observed that Excel 365 returns “30 Jun” for both =TEXT(“30 Jun”,“dd-mmm”) and =TEXT(“Jun 30”,“dd-mmm”). On the other hand on this test machine following code was tested:

Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
System.out.println(ws.calculateFormula("=TEXT(\"30 Jun\",\"dd-mmm\")"));
System.out.println(ws.calculateFormula("=TEXT(\"Jun 30\",\"dd-mmm\")"));

Following is the output:
01-Jun
30-Jun

Here we can see that in the later case Aspose.Cells for Java 19.4.x returns 30-Jun. Please confirm if you are getting same results. Once confirmed, I will log a ticket for this issue.

8

Hi,

Thanks for the update.

Yes, Aspose returns different from Excel. We are expecting Aspose returns the same result as Excel, i.e. “30-Jun” for both.

Thanks,
Hui

9

@huichen,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as
CELLSJAVA-42911 - Issue while using TEXT function for dates

10

@huichen,

This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-42911”) now. We will provide you the fixed version by next week after performing QA and incorporating other enhancements and fixes.

11

The issues you have found earlier (filed as CELLSJAVA-42911) have been fixed in Aspose.Cells for Java 19.5. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

12

Hi,

I confirmed this issue has been fixed in v19.5. Thank you very much for the kind help and quick fix!

Regards,
Hui Chen

13

@huichen,

Good to know that your issue is sorted out by the new version/fix. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.