https://bugs.documentfoundation.org/show_bug.cgi?id=161043
--- Comment #7 from ady <[email protected]> --- (In reply to Colin from comment #6) > Excel gets it right so besides data loss, there's the compatibility issue. Let's be accurate then. Is 547:12:52 an incorrect calculation? IIUC, it is correct; please clarify if it isn't. There is no data loss. I have not modified the formulas nor the functions, so SUBTOTAL(9;range) is not failing in any way. There is indeed a cell format issue. A clock and/or calendar time/date values are not the same as timer/period value. You want the latter. A cell format such as: MM:SS is not a timer/period cell format, but a calendar/clock cell format. So, changing it to: [HH]:MM:SS makes sense in terms of what you want to achieve. OTOH, changing it to: HH:MM:SS would be incorrect for your case, because that is a calendar/clock cell format, and that is not what you need. With each of these formats: _ MM:SS _ HH:MM:SS _ [HH]:MM:SS _ (clear direct cell formatting) you see a different result in the cell, but it does not mean that the SUBTOTAL function has a bug because of such different results. Regarding what Excel does... Calc probably lacks some needed enhancements regarding supporting cell formats. In particular, the whole set of issues about date/time cell formats and calculations is almost never-ending, in whichever spreadsheet tool you would mention, including Excel. As for compatibility, ODS is the natural file format in Calc, based on the ODF standard. Excel is (or should be) following the same standard. Excel might be better in cell formatting in general (in its own XLSX file format) and in some algorithm that assumes what the user wants to achieve, but it could also be incorrect in that assumption. In this particular case, the assumption apparently was adequate. You could open a new ticket with an enhancement request – Calc needs to be more "intelligent" regarding "timer" cell formats, but please be specifically focused on it, with a clear simple basic use-case and what the exact expected behavior should be. Please be prepared to opposite cases, in which Calc might need to assume a different behavior than what you suggest should be, in order to distinguish between those; developers should be able to discern those cases in order for Calc to make accurate and useful assumptions, without major negative consequences for other use-cases. If I may a humble suggestion, I would suggest first for you to acquire more insight regarding cell formatting, so the enhancement request would have a clear presentation and arguments. As far as I can understand up to this point, this particular ticket seems to be NAB, unless 547:12:52 is wrong (too). -- You are receiving this mail because: You are the assignee for the bug.
