Issue for TEXT function



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”.



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



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:
Sample code:

Workbook workbook = new Workbook("f:\\files\\Bk_textfunc1.xlsx");
		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 (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).


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?




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?


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?



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:

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.



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.



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



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.


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



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

Hui Chen



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.