ONLYOFFICE Docs v6.4 with conditional formatting, new scaling options, and WOPI protocol support
ONLYOFFICE Docs v6.4 released

EOMONTH formula consistently shows the day before the end of the month

OS version: Windows 10
App version: 6.4.1.46
Downloaded from: ONLYOFFICE website

The EOMONTH formula in the spreadsheet editor consistently shows the day before the end of the month. Here is an example for each month in 2020 and 2021 using month-offset values of 0, 1, and -1:

I also included the example from the official help center page on the EOMONTH formula that includes a screenshot of the same formula giving a serialized date of 43496 (1/31/2019), whereas my editor calculates 43495 (1/30/2019).

The same spreadsheet opened in Excel calculates the actual end of month values, but apparently as a new user I can’t include that screenshot as well.

I can reply with the Excel results of the same spreadsheet though:

Greetings!

Unfortunately we were unable to reproduce this issue. Please provide us with the file you used for your screenshots, specify the formatting you are using for your date cells and provide us with a screenshot of your “number format” window.

Best regards.

Do you have a place I can upload the xlsx file to, or an email address to send it to? I’m not able to attach it here.

The formatting is the default date format for every value (with the exception of cell G2 just using general formatting to match the help center screenshot):

Greetings!

Please provide us with the .xlsx file by sending it to 4test@onlyoffice.com.

Best regards.

Ok, I just sent it.

Could you please perform the following:

  1. Specify if the file you provided was initially created using ONLYOFFICE editors.
  2. Specify if this problem reproduces in every newly created (in ONLYOFFICE editor) xlsx file.
  3. Try to open up this file in your ONLYOFFICE editor and click Formula => Calculation => Calculate workbook and see if it changes the output of the formula to the correct value.
  4. Provide us with a screenshot of the File => Advanced Settings… => General tab of your ONLYOFFICE editor.

Best regards.

  1. Yes, this file was created in ONLYOFFICE.
  2. Yes, this happens in every brand new xlsx file in ONLYOFFICE.
  3. I followed these steps, nothing changed.
  4. Sure, here you go:

We also use a locally hosted ONLYOFFICE workspace and it’s experiencing the same bug. I believe we’re on version 11.5.4.1582. I created a brand new spreadsheet directly in workspace and edited it exclusively in the web app and came up with the same results:

image

I can only post one image at a time so here’s the same general settings page from the ONLYOFFICE workspace spreadsheet editor:

This even happens on the android mobile app.

If I had to guess based on how broad the issue is and that a European team can’t replicate it, it’s a timezone problem. I’m in UTC-5 and the formula is coming up with a correct date in UTC then shifting it back to my local time before returning the result which is truncated to the day prior.

Could you please designate the severity of this particular issue for you?

Best regards.

Low-medium I guess?

Working around it by re-adding the missing day means that it’s incompatible with Excel users in the company which we also have. I can think of another more complicated way to calculate an end of the month but it’ll be a lot more steps than running EOMONTH which really should just work as described.

We have registered a bug (number 53003 in our internal tracker). We’ll try to include the fix in the next Document Server version.