EOMONTH formula/function calculation error on Russian locale

Aspose.Cells Product Family
1

Hi. We have found, that function EOMONTH in some case calculates wrong. Please check following code examples:

@Test
public void eomonth_success_test() throws Exception {
Workbook workbook = new Workbook("C://in.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setTransitionEvaluation(false);
workbook.calculateFormula();
Cell cell = worksheet.getCells().get("B2");
assertEquals("30.09.2020", cell.getDisplayStringValue());
}

@Test
public void eomonth_fail_test() throws Exception {
Workbook workbook = new Workbook("C://in.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setTransitionEvaluation(true);
workbook.calculateFormula();
Cell cell = worksheet.getCells().get("B2");
assertEquals("30.09.2020", cell.getDisplayStringValue());
}

in.zip (6.5 KB)

Best regards. Alexey

2

@makarovalv,

Please notice, we have logged a ticket with an id “CELLSJAVA-43305” for your issue. We will look into it soon. The issue logged as following:
CELLSJAVA-43305 - EOMONTH formula/function calculation error on Russian locale

Once we have an update on it, we will let you know.

3

@makarovalv,
What is the expected result for the formula? The recognizable datetime value for EOMONTH depends on the regional settings. For your template, “04.09.2020” cannot be taken as a valid datetime value with common regions such as en_US and the formula

=EOMONTH("04.09.2020",0)

will give #VALUE! in ms excel too. To get the expected result, you should specified the region as Russia. Example code:

Workbook workbook = new Workbook("C://in.xlsx");
workbook.getSettings().setRegion(CountryCode.RUSSIA);
...

In this way the formula will be calculated as expected.

Let us know your feedback.

4

Hi.

For Russian region it is regular date format dd.MM.yyyy

There is screenshot of Excel with this formula
screen.png (9.9 KB)

Also, even with Russian region, one of tests fails image.png (97.5 KB)

Best regards. Alexey

5

@makarovalv,

Thanks for your feedback with screenshots.

We will evaluate your issue further and get back to you with new information.

6

@makarovalv,

We evaluated your issue in details.

we got result (for the last line “cell.getDisplayStringValue()”) as “31/1/1900” which is fine. We also tested in MS Excel and got the same results. the value MS Excel gives is also 31. In short, this is not an issue with the APIs and we cannot fix it.

7

Hi.

We have performed some investigation about this issue. Please, check following test:

@Test
public void eomonth_fail_test() throws Exception {
    Locale.setDefault(new Locale("ru", "RU"));
    Workbook workbook = new Workbook("C://work/in.xlsx");
    workbook.getSettings().setRegion(CountryCode.RUSSIA);
    Worksheet worksheet = workbook.getWorksheets().get(0);
    worksheet.setTransitionEvaluation(true);
    workbook.calculateFormula();
    Cell cell = worksheet.getCells().get("B2");
    assertEquals("30.09.2020", cell.getDisplayStringValue());
}

@Test
    public void eomonth_success_test() throws Exception {
        Locale.setDefault(new Locale("ru", "RU"));
        Workbook workbook = new Workbook("C://work/in.xlsx");
        workbook.getSettings().setRegion(CountryCode.RUSSIA);
        Worksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.setTransitionEvaluation(false);
        workbook.calculateFormula();
        Cell cell = worksheet.getCells().get("B2");
        assertEquals("30.09.2020", cell.getDisplayStringValue());
    }

The difference between this code is only “transitionEvaluation” flag. Can you fix this behavior?

Best regards. Alexey

8

@makarovalv,

Thanks for providing us code segment.

We will evaluate it and get back to you soon.

9

Hi. Do you have any news about this issue?

Best regards. Alexey

10

@makarovalv,

I guess you are talking about the test case you provided in the post, is not it? We are very sorry but how could we solve it in your way only. It looks you want to get same result for different values of TransitionEvaluation option. We cannot discard this option for the sake of your requirements. We have to keep the behavior same and consistent with MS Excel. Also, there are other users who need this option which works in the same way with MS Excel.

Thanks for your understanding!

11

Hi.

So, can you explain why TransitionEvaluation affects this calculation in this wrong way? What kind of behavior changes, when we set on TransitionEvaluation.

Please note, that MS Excel correctly performs this formula for both TransitionEvaluation modes (in Russian locale).

Best regards. Alexey

12

@makarovalv,

We will evaluate it further and get back to you with more details.

13

@makarovalv,

We found when we change the TransitionEvaluation mode for your template file (in.xlsx) with the locale set as Russian, if TransitionEvaluation option is checked, cell B2 shows “31.01.1900”. But when uncheck this option, cell B2 shows “30.09.2020”. So we do not get the same results for this option as you said, even though with Russian locale.

14

Hi. I apologase, i was misled. MS Excel really works incorrectly when TransitionEvaluation is set on. So you can close this issue.

Best regards. Alexey

15

@makarovalv,

Thanks for your confirmation and glad to hear that you understand the case now. In the event of further queries or issue, feel free to contact us any time, we will be happy to assist you soon.