Description: Time values which individually are only a few minutes and seconds but accumulate to many hours are being presented as just the "mantissa" portion of the the total time ie. the cumulative total of 547:12:52 in the attached sample only returns 12:52:00. Steps to Reproduce: The seconds in column C have been converted to minutes and seconds by the use of =TIME(;;Cn) in the adjacent column D. The mask has been set to just present MM:SS. The individual calculations are correct unless the seconds (3600) are equal to or exceed 60 minutes whereby it just presents the "mantissa" minutes and seconds. One could argue that an incorrect mask is the culprit but there is no indication that the number has overflowed into hours and I would argue that when it does overflow but doesn't indicate perhaps 65:25 it is incorrect. The SUBTOTAL() function at the top has not been limited by a mask and still only presents the "mantissa" for the correct total of 547:12:52. Actual Results: Hours are dropped/discarded from all "masked" calculations Expected Results: 1. Accumulate and display any excess minutes within the confines of the mask ie. 65:25 or 2. Indicate an error condition to permit reassessment of the mask or 3. Force a more appropriate mask Reproducible: Always User Profile Reset: No Additional Info: Version: 7.5.9.2 (X86_64) / LibreOffice Community Build ID: cdeefe45c17511d326101eed8008ac4092f278a9 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: en-GB (en_GB); UI: en-GB Calc: threaded
Created attachment 194087 [details] Simple Calc Demonstrating the Error
What if you use to format the cell [HH]:MM:SS,00 https://help.libreoffice.org/24.2/en-US/text/shared/01/05020301.html?&DbPAR=CALC&System=WIN#:~:text=to%20display%20hours%2C%20minutes%20and%20seconds%20use%20the%20following%20number%20format%20codes%3A
(In reply to m_a_riosv from comment #2) > What if you use to format the cell > [HH]:MM:SS,00 Or [HH]:MM:SS.00 if your decimal separator is the period dot rather than the comma.
(In reply to ady from comment #3) > (In reply to m_a_riosv from comment #2) > > What if you use to format the cell > > [HH]:MM:SS,00 > > Or [HH]:MM:SS.00 if your decimal separator is the period dot rather than the > comma. Both this and SS,00 make matters worse; The cell fills with pointless empty values and the subtotal() still thinks three weeks is a little over 12 hours. Neither of the formats accumulates 65 minutes as 65:00 which is what I would expect. Excel, given the same structure (but I didn't experiment with a single cell accumulating 65:00), produces the correct SUBTOTAL() If I force the format 876613:37:46 into cell D1 it correctly accumulates all the "integer" hours - I have no idea where that format originated, I just discovered it playing with the above suggestions.
(In reply to Colin from comment #4) > Both this and SS,00 make matters worse; In my case, I open attachment 194087 [details], cell D1, [CTRL]+[1] > the format shows: MM:SS.00 and I change it to: [HH]:MM:SS Accept the new cell format. If needed, expand the width of the column. Cell D1 now shows 547:12:52. I have not modified any other cell. Tested with LO 7.5.3.2 and recent 24.8 alpha.
(In reply to ady from comment #5) > (In reply to Colin from comment #4) > > > Both this and SS,00 make matters worse; > > In my case, I open attachment 194087 [details], cell D1, [CTRL]+[1] > the > format shows: > MM:SS.00 > and I change it to: > [HH]:MM:SS > > Accept the new cell format. If needed, expand the width of the column. Cell > D1 now shows 547:12:52. I have not modified any other cell. > > Tested with LO 7.5.3.2 and recent 24.8 alpha. I'm now well aware that I can compromise reality to satisfy LO but ever since I was about 5, I understood that 27 minutes and 37 minutes was 64 minutes and I could CHOOSE to represent that as 64 minutes or 1 hour and 4 minutes where both are correct. but 4 is an error. Not only does LO insist that 37 and 27 is 4 - SUBTOTAL()ing 7779 smaller amounts to arrive at 12 hours and 52 minutes instead of 3 Weeks+. I didn't get a job as an accountant because they asked me to SUM 2 + 2 and when I said FIVE - they acknowledged that it was "close enough" so they gave me the job. It would be understandable if LO identified that the data doesn't fit and made automatic adjustments to masks with the appropriate "heads-up" but nobody will ever convince me that 37+27=4. Excel gets it right so besides data loss, there's the compatibility issue.
(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).
Created attachment 194093 [details] Screenshot LibreOffice vs Excel For me Excel shows the same result as LibreOffice, for the attached sample file.
(In reply to m_a_riosv from comment #8) > For me Excel shows the same result as LibreOffice, for the attached sample > file. IIUC, I think that @Colin meant that (for him or in his system) Excel shows cell D1 as 547:12:52 whereas Calc shows the same cell D1 as 12:52:00, without having to modify the cell format of that specific cell ("MM:SS"). I don't see the same as @Colin's claims. In any case, I don't see a bug in the SUBTOTAL function in Calc.