How does it works the built-in number format with value 14?

Aspose.Cells Product Family
,
1

Hello,
could you help me to understand the display value of a cell that use “14” (m/d/yy) as built-in number format, please?
We use Aspose Cells 24.1 and we have used this documentation to implement our code.

This is our example:

Workbook workbook = new Workbook();
workbook.getSettings().setLocale(Locale.ENGLISH);
Worksheet worksheet = workbook.getWorksheets().get(0);
Style style = worksheet.getCells().get("A1").getStyle();
style.setNumber(14);
worksheet.getCells().get("A1").putValue(DateTime.getNow());
worksheet.getCells().get("A1").setStyle(style);
System.out.println("Format m/d/yy built-in  -> result: " + worksheet.getCells().get("A1").getDisplayStringValue());

Using Locale.ENGLISH, we expecte 2/8/24 but we have 2/8/2024
Using Locale.ITALIAN, we expecte 8/2/24 but we have 08/02/2024

If we use style.setCustom(“m/d/yy”) instead of style.setNumber(14), the result is that we expected.

Using Locale.ENGLISH, we expecte 2/8/24 and we have 2/8/24
Using Locale.ITALIAN, we expecte 2/8/24 and we have 2/8/24

We attach a test that reproduce the scenario.
Thanks,
Regards,
Giulio

TestDateFormatValue.zip (763 Bytes)

2

@giulio.andolfi,

Thanks for the sharing the details and sample.

After an initial testing, I noticed the issue/behavior you have described for the built-in number format (value) “14” (m/d/yy). We will evaluate if this expected behavior or an issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-45849

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

3

@giulio.andolfi

For built-in number format 14, the pattern changes according to the regional settings of the environment for both of ms excel and Aspose.Cells. So, please check the environment firstly:

  1. Please check the default date time format of the regional settings for the environment where to run the application with Aspose.Cells, If the year pattern is “yyyy”, then the application should give the result as “yyyy” instead of “yy”.

  2. For Aspose.Cells for Java, we get the pattern from the DateFormat instance of DateFormat.SHORT. So please check whether this instance can give the expected result as “yy” instead of “yyyy”. Code example:

    DateFormat.getDateInstance(DateFormat.SHORT, locale).format(new java.util.Date());

If it gives “yyyy” instead of the expected “yy”, we are afraid there are some issue with the configuration of regional settings for the java environment itself.

4

@giulio.andolfi
The description of 14 in the document is incorrect.
It should be m/d/yyyy in US.
Sorry for confusion.
We will correct it soon.

5

Many thanks @simon.zhao.
We will change it in our code and documentation.
Rergards,
Giulio

6

@giulio.andolfi
You are welcome. If you have any questions, please feel free to contact us.