Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 07 Feb 2014 12:26:19 +0100How to calculate minutes per kilometer?https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/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.Sun, 02 Feb 2014 09:13:47 +0100https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/Answer by w_whalley for <p>Hi.</p>
<p>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. </p>
<p>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.</p>
https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?answer=29255#post-id-29255There 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. Sun, 02 Feb 2014 17:57:57 +0100https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?answer=29255#post-id-29255Comment by ROSt52 for <p>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</p>
<p>LO's default time format is <code>HH:MM:SS AM/PM</code> (check it with Format Cells when the cell is selected). You can change the format it to <code>HH:MM:SS</code> by deleting the <code>AM/PM</code> in the Format Cells/Format Code box. </p>
https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?comment=29530#post-id-29530@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.Fri, 07 Feb 2014 12:26:19 +0100https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?comment=29530#post-id-29530Comment by w_whalley for <p>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</p>
<p>LO's default time format is <code>HH:MM:SS AM/PM</code> (check it with Format Cells when the cell is selected). You can change the format it to <code>HH:MM:SS</code> by deleting the <code>AM/PM</code> in the Format Cells/Format Code box. </p>
https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?comment=29316#post-id-293161 day x 24 hours/day x 60 min/hour, so 1 day = 1440 min.Mon, 03 Feb 2014 15:17:05 +0100https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?comment=29316#post-id-29316Comment by ROSt52 for <p>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</p>
<p>LO's default time format is <code>HH:MM:SS AM/PM</code> (check it with Format Cells when the cell is selected). You can change the format it to <code>HH:MM:SS</code> by deleting the <code>AM/PM</code> in the Format Cells/Format Code box. </p>
https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?comment=29291#post-id-29291@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.Mon, 03 Feb 2014 06:59:16 +0100https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?comment=29291#post-id-29291Answer by ROSt52 for <p>Hi.</p>
<p>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. </p>
<p>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.</p>
https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?answer=29244#post-id-29244It 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.Sun, 02 Feb 2014 12:11:50 +0100https://ask.libreoffice.org/en/question/29240/how-to-calculate-minutes-per-kilometer/?answer=29244#post-id-29244