[libreoffice-users] Re: Can't Sum() time measurements
Am 08.08.2012 18:12, teknow wrote: I have a list of measured times in [H]:MM:SS format and I would like to sum them. If I put in a formula: = B2+B3+B4+B5 it works, but if I try: =Sum(B2:B5) I get 00:00:00 as a result. What am I missing? I have tried both LibO 3.5.5.3 and 3.6.0.4 Thanks. Works for me with all numbers but not with text: http://www.mediafire.com/file/la4iwk9q3vm64be/times_add.ods LibreOffice and Excel suffer from the same bug. They do an implicit conversion from string to number when arithmetic operators are used with strings. The arithmetic functions are designed to ignore all strings. A properly programmed spreadsheet application would never convert any string unless you tell it to do so and it would throw a #VALUE! error whenever a string comes in the way of an arithmetic operation. Unfortunately, typical Excel users prefer easy data over correct ones which leads to massive confusion about very simple facts. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Can't Sum() time measurements
Thanks for the responses. I'm being given an already filled-out .xlsx, but I'm not seeing a difference if I save it to .ods. The cells are identified as in H:MM format, but the actual measurements are in minutes and seconds (with a few H:MM:SS sprinkled in). It's a bit confusing because what I see in the Cell is 1:18 as in one minute and eighteen seconds, but what I see in the input line is 01:18:00 AM. The hours and minutes makes sense, but what is that AM doing there? If I switch to text format, I get decimal numbers (e.g. 0.05416667 in the 1:18 cell). Completely not understanding, I was doing some text manipulation (copy to notepad, add 00: to the front end, copy back) to try to get the values recognized as minutes and seconds, and that appears to have caused the screw-up. I suppose pursuing why those cells add like time (seconds roll over to minutes at 60, etc.) when they only look like time values, but don't sum() at all should be left for another day. If I ignore the entries with hours (which I suspect are erroneous anyway) and simply divide the original cells by 60, and set the formatting to [H]:MM:SS, I appear to be getting what I need and sum() works as I would expect. Sorry for the confusion. -- View this message in context: http://nabble.documentfoundation.org/Can-t-Sum-time-measurements-tp347p413.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Can't Sum() time measurements
2012/8/8 teknow teknowledg...@gmail.com: Thanks for the responses. I'm being given an already filled-out .xlsx, but I'm not seeing a difference if I save it to .ods. The cells are identified as in H:MM format, but the actual measurements are in minutes and seconds (with a few H:MM:SS sprinkled in). It's a bit confusing because what I see in the Cell is 1:18 as in one minute and eighteen seconds, but what I see in the input line is 01:18:00 AM. The hours and minutes makes sense, but what is that AM doing there? If I switch to text format, I get decimal numbers (e.g. 0.05416667 in the 1:18 cell). 0.05416667=1:18:00 (1 hour, 18 minutes) 1.0=24:00:00 0.5=12:00:00 0.25=06:00:00 and so on. To be exact, 0.5 really means 1899-12-30 12:00:00 (0.5 days since 1899-12-30 00:00:00), the same for 1.0 which then means 1899-12-31 00:00:00. Formatting to a pure time format, such as HH:MM:SS ”hides” the date, so we don't need to bother about irrelevant years, months and days, for example when you sum times. The format you see in your input line is probably dependent on your language settings, therefore the AM in your case and not in my case. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ Completely not understanding, I was doing some text manipulation (copy to notepad, add 00: to the front end, copy back) to try to get the values recognized as minutes and seconds, and that appears to have caused the screw-up. I suppose pursuing why those cells add like time (seconds roll over to minutes at 60, etc.) when they only look like time values, but don't sum() at all should be left for another day. If I ignore the entries with hours (which I suspect are erroneous anyway) and simply divide the original cells by 60, and set the formatting to [H]:MM:SS, I appear to be getting what I need and sum() works as I would expect. Sorry for the confusion. -- View this message in context: http://nabble.documentfoundation.org/Can-t-Sum-time-measurements-tp347p413.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
