DocumentServer
DocumentServer copied to clipboard
ONLYOFFICE
Spreadsheet number format negative time
Negative times would also be ideal for a time sheet (spreadsheet).
Should work time 40:00:00 Working time is 38:00:00 Total -02: 00: 00
Why are there only positive time formats? Can the formats be adjusted somewhere compared to Excel?
Hello, @vogych. I understood you correctly that you want a negative time to be displayed in the cell (as LibreOffice )?
Yes exactly!
In Excel I can simply expand the existing formats, with the time format 00:00:00 I can simply put a minus in front ... example -00:00:00
Should work time 40:00:00 Working time is 38:00:00 Total -02: 00: 00
Can you attach XLSX file that can demonstrate the time calculation you described?
Please find attached the XLSX file timesheet. Timesheet.xlsx
Number format is [h]:mm;- [h]:mm;0:00 in combination with 1904 date system.
Making negative times possible at all with 1904 date system: https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system
Can I adjust the 1904 date system in OnlyOffice?
@vogych, thanks for the details.
Creating a custom format is know issue 38828 in our private issue tracker. 1904 date system is only supported only on opening at the moment. I created new issue 45622 to think about it with our team.
@Rita-Bubnova I do have the same issue and it actually occurred to me in the same use-case. I wrote a small work-time tracking spreadsheet and "negative" overtime is not being displayed. Oddly though, the computations do still work: I am tracking overtime per week and total overtime. In one week my overtime was negative and the cell only displayed "########", but the total overtime got correctly reduced by the entry in this cell.
same issue for me, also need this feature please. Its also not possible to create custom time format with >24h. See here: https://github.com/ONLYOFFICE/DesktopEditors/issues/682
same issue still for me. this is disturbing, that only-office cant show negative time. is there a timeline for an solution?
The issue consist of 2 different problem (see comment):
Creating a custom format is know issue 38828 in our private issue tracker.
This issue was fixed (https://github.com/ONLYOFFICE/sdkjs/commit/4e9d12f30a34a44bb7fa0266aef4ffb6ccd72ecd and https://github.com/ONLYOFFICE/web-apps/commit/3cdd036ba74b7d7d350ae486e22f49495947b775) in version 6.2.0.
1904 date system is only supported only on opening at the moment. I created new issue 45622 to think about it with our team.
This issue actual in Document Server version 7.1.
So this issue is partly fixed, I'll leave this open, but please comment any further only if you got troubles with 1904-date system
For any other negative format number it's better to create a new issue
The issue about support 1904 date system fixed (https://github.com/ONLYOFFICE/sdkjs/commit/8b84700ad8eb912682b984dac3f69b1a51d0123d, https://github.com/ONLYOFFICE/web-apps/commit/98a418f7e33f620de5dfc14f24853c479001771a, https://github.com/ONLYOFFICE/web-apps/commit/8e6ad3b0c685482934fceac60e0efc8433ca2f07) in future major release.
DocumentServer v7.2 is released. This issue should be fixed
Feel free to comment or reopen it if you got further questions
Yesterday I've upgraded to version 8.0 after seeing that 7.x doesn't still allow me to format a cell as time which contains a negative value.
So we probably should reopen this issue?
Hello @whimboo, I would like to clarify that the ability to work with negative time is only available when using the 1904 date system. Please check if this setting is enabled for you. To do this, go to: File - Advanced Settings and check the box for "Use 1904 date system."
If the issue persists, please provide a more detailed description. Thank you.
