Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 03 Nov 2014 05:14:43 +0100Time formats in Calc suitable for use in calculationshttps://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/Hello.
I have a spreadsheet into which I am importing time values. These are elapsed times for walking/running activities. They appear in the spreadsheet as 1:10:36 (hours-mins-secs) or 58:20 (min-secs).
In this format I can't use them in calculations for working out average speed and/or average pace. The calculations happen but give wildly incorrect results.
Is there a suitable format available that will convert these to decimal hours that I can use in calculations? If not can anyone suggest a formula I could use to do the conversion?
Thanks in advance.Thu, 30 Oct 2014 04:19:05 +0100https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/Answer by ObrienDave for <p>Hello.</p>
<p>I have a spreadsheet into which I am importing time values. These are elapsed times for walking/running activities. They appear in the spreadsheet as 1:10:36 (hours-mins-secs) or 58:20 (min-secs).</p>
<p>In this format I can't use them in calculations for working out average speed and/or average pace. The calculations happen but give wildly incorrect results.</p>
<p>Is there a suitable format available that will convert these to decimal hours that I can use in calculations? If not can anyone suggest a formula I could use to do the conversion?</p>
<p>Thanks in advance.</p>
https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?answer=41819#post-id-41819LibreOffice, OpenOffice, Excel, etc., do NOT calculate times as hours, minutes, and seconds.
You have to convert them to fractions of a day or use the format string for elapsed time.
So, your 1:10:36 works out to 0.0490277777777778 days.
Your 58:20 is 0.0405092592592593 days.
For example, running 5 miles in 00:58:20 should be 5/0.0405092592592593/**24** to give you 5.1428571429 miles per hour
Add /24 at the end of your formulaThu, 30 Oct 2014 06:25:51 +0100https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?answer=41819#post-id-41819Comment by joe.aquilina.92 for <p>LibreOffice, OpenOffice, Excel, etc., do NOT calculate times as hours, minutes, and seconds.</p>
<p>You have to convert them to fractions of a day or use the format string for elapsed time.</p>
<p>So, your 1:10:36 works out to 0.0490277777777778 days.</p>
<p>Your 58:20 is 0.0405092592592593 days.</p>
<p>For example, running 5 miles in 00:58:20 should be 5/0.0405092592592593/<strong>24</strong> to give you 5.1428571429 miles per hour</p>
<p>Add /24 at the end of your formula</p>
https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?comment=41823#post-id-41823Thanks for that suggestion. Adding the /24 at the end of the calculation I already found and tested. Using the /24 seems to work ok when the elapsed time is 1hour or more. However it still doesn't work if the time is < 1 hour. Calc interprets a time like 55:30 as 55 hours 30 mins rather than the 55 mins 30 second it should be. If I manually pad it out to change 55:30 to 00:55:30, it works just fine. My raw data doesn't pad it out for me; I will just do that for now.
Any other suggestions?Thu, 30 Oct 2014 07:38:29 +0100https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?comment=41823#post-id-41823Answer by joe.aquilina.92 for <p>Hello.</p>
<p>I have a spreadsheet into which I am importing time values. These are elapsed times for walking/running activities. They appear in the spreadsheet as 1:10:36 (hours-mins-secs) or 58:20 (min-secs).</p>
<p>In this format I can't use them in calculations for working out average speed and/or average pace. The calculations happen but give wildly incorrect results.</p>
<p>Is there a suitable format available that will convert these to decimal hours that I can use in calculations? If not can anyone suggest a formula I could use to do the conversion?</p>
<p>Thanks in advance.</p>
https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?answer=41864#post-id-41864Partial success achieved but clearly I am not understanding something here.
Doing some research and experimenting, I have discovered that if I display the time value as a 4 digit decimal number and multiplying by 24, I can achieve a calculation that gives the expected value for pace/speed. However that is only true when the time is in the form hh:mm:ss. If I have a time that is mm:ss, it is being interpreted as hh:mm and the calculation is still incorrect.
Any help would be appreciated. In the meantime, I will continue to experiment.Fri, 31 Oct 2014 09:49:30 +0100https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?answer=41864#post-id-41864Comment by ObrienDave for <p>Partial success achieved but clearly I am not understanding something here.</p>
<p>Doing some research and experimenting, I have discovered that if I display the time value as a 4 digit decimal number and multiplying by 24, I can achieve a calculation that gives the expected value for pace/speed. However that is only true when the time is in the form hh:mm:ss. If I have a time that is mm:ss, it is being interpreted as hh:mm and the calculation is still incorrect.</p>
<p>Any help would be appreciated. In the meantime, I will continue to experiment.</p>
https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?comment=41875#post-id-41875Can you paste a small amout of your data in a spreadsheet, please?
I have an idea.Fri, 31 Oct 2014 11:11:07 +0100https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?comment=41875#post-id-41875Comment by joe.aquilina.92 for <p>Partial success achieved but clearly I am not understanding something here.</p>
<p>Doing some research and experimenting, I have discovered that if I display the time value as a 4 digit decimal number and multiplying by 24, I can achieve a calculation that gives the expected value for pace/speed. However that is only true when the time is in the form hh:mm:ss. If I have a time that is mm:ss, it is being interpreted as hh:mm and the calculation is still incorrect.</p>
<p>Any help would be appreciated. In the meantime, I will continue to experiment.</p>
https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?comment=41976#post-id-41976Happy to do that but how do I put a small spreadsheet in here? Maybe I am missing something but I can't see any option to do that.Mon, 03 Nov 2014 05:14:43 +0100https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?comment=41976#post-id-41976Answer by ROSt52 for <p>Hello.</p>
<p>I have a spreadsheet into which I am importing time values. These are elapsed times for walking/running activities. They appear in the spreadsheet as 1:10:36 (hours-mins-secs) or 58:20 (min-secs).</p>
<p>In this format I can't use them in calculations for working out average speed and/or average pace. The calculations happen but give wildly incorrect results.</p>
<p>Is there a suitable format available that will convert these to decimal hours that I can use in calculations? If not can anyone suggest a formula I could use to do the conversion?</p>
<p>Thanks in advance.</p>
https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?answer=41867#post-id-41867I looked into this matter and came to an not yet understood observation, thus placed a question: http://ask.libreoffice.org/en/question/41866/strange-time-display/Fri, 31 Oct 2014 10:05:11 +0100https://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/?answer=41867#post-id-41867