Ask Your Question

Time formats in Calc suitable for use in calculations [closed]

asked 2014-10-30 04:19:05 +0100

joe.aquilina.92 gravatar image

updated 2014-10-30 04:28:20 +0100


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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 19:03:26.649814

3 Answers

Sort by » oldest newest most voted

answered 2014-10-30 06:25:51 +0100

ObrienDave gravatar image

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

edit flag offensive delete link more


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?

joe.aquilina.92 gravatar imagejoe.aquilina.92 ( 2014-10-30 07:38:29 +0100 )edit

answered 2014-10-31 09:49:30 +0100

joe.aquilina.92 gravatar image

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.

edit flag offensive delete link more


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

I have an idea.

ObrienDave gravatar imageObrienDave ( 2014-10-31 11:11:07 +0100 )edit

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.

joe.aquilina.92 gravatar imagejoe.aquilina.92 ( 2014-11-03 05:14:43 +0100 )edit

answered 2014-10-31 10:05:11 +0100

ROSt52 gravatar image

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

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-10-30 04:19:05 +0100

Seen: 6,400 times

Last updated: Oct 31 '14