How to calculate minutes per kilometer?


In Excel you have a formula that calculates minutes per kilometer automatically and I wonder how I can do this in Calc. I am organising a run and for the result I use normally Excel but want to change to Calc.

So for a race that is 9,75km long - if a user uses 00:45:23 (o hrs, 45mins, 23sec) in Excel I normally just write Time/Distance and I get Minutes per km. Not sure how I can do this in Calc. Do I need to create a macro? I tried today with Calc 4.2 and it is the same.

There is a somewhat simpler formula; =B1*1440/A1
Explanation: If you enter the time in B1 as “00:45:23” (without the quotes) then LO converts it to a time value. The time value is a fraction of a day. Multiplying by 1440 converts days to minutes. Dividing by A1 (km) gives Minutes/km

LO’s default time format is HH:MM:SS AM/PM (check it with Format Cells when the cell is selected). You can change the format it to HH:MM:SS by deleting the AM/PM in the Format Cells/Format Code box.

@w_whalley - “Multiplying by 1440 converts …” is a very smart way to do this calculation!!! Unfortunately I neither found the “1440” nor similar value for other time units in the manual or help file.

1 day x 24 hours/day x 60 min/hour, so 1 day = 1440 min.

@w_whalley - I should be able to memorize this… :wink: Thanks.
BTW, based on youir excellent comment on the “1440” , I placed an enhancement request for a function which incorporates this. Should be fairly simple to realize such functions.

It seems to be bit more tricky in Calc compared to what you wrote about Excel.

The solution I found is:

A1 is distance in km
B1 is time in hh:mm:ss format

You basically convert first the time in minutes and divide it by the distance.

You also can have a look at add-in functions to see if there is one which converts B1 directly into minutes.