Ask Your Question
1

How to calculate minutes per kilometer? [closed]

asked 2014-02-02 09:13:47 +0200

brynekaren gravatar image

Hi.

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.

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-02-19 06:04:16.238683

2 Answers

Sort by » oldest newest most voted
4

answered 2014-02-02 17:57:57 +0200

w_whalley gravatar image

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.

edit flag offensive delete link more

Comments

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

ROSt52 gravatar imageROSt52 ( 2014-02-03 06:59:16 +0200 )edit

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

w_whalley gravatar imagew_whalley ( 2014-02-03 15:17:05 +0200 )edit

@w_whalley - I should be able to memorize this...... ;-) Thanks. BTW, based on youir excellent comment on the "1440" , I placed an enhancement request for a function which incorporates this. https://bugs.freedesktop.org/show_bug.cgi?id=74426 Should be fairly simple to realize such functions.

ROSt52 gravatar imageROSt52 ( 2014-02-07 12:26:19 +0200 )edit
2

answered 2014-02-02 12:11:50 +0200

ROSt52 gravatar image

updated 2014-02-02 12:15:27 +0200

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

The solution I found is:
=(HOUR(B1)*60+MINUTE(B1)+SECOND(B1)/60)/A1

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

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-02-02 09:13:47 +0200

Seen: 9,372 times

Last updated: Feb 02 '14