Time formats in Calc suitable for use in calculations

English
,
#1

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.

Strange time display
#2

LibreOffice, 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 formula

#3

Thanks 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?

#4

Partial 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.

#5

Can you paste a small amout of your data in a spreadsheet, please?

I have an idea.

#6

Happy 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.

#7

I looked into this matter and came to an not yet understood observation, thus placed a question: Strange time display

#8

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.