Whenever I try to calculate a running pace (minutes/per mile) it returns a
number in decimal form. But the decimal then has to be converted to seconds.


For example, I want to calculate my pace (minutes/mile) if I ran 4 miles in
31 minutes and 12 seconds. To get an accurate pace, I have to convert 31
minutes and 12 seconds to a decimal format which would be equal to 31.2
minutes. (12 seconds divided by 60 seconds = .2)


Then when I divide 31.2 minutes by 4.0 miles the answer I get in Excel is
7.80.

Since there are only 60 seconds in a minute, 7.80 is actually 7 minutes and
48 seconds per mile. (0.8 x 60 seconds) Is there a function in Excel that
can convert decimals to minutes and seconds without having to do some
advanced programming?

Any ideas?

Thanks,
RuSL

If A2 is your number of minutes as a decimal number, then use =A2/(24*60)
and format as time (such as [m]:ss). [This works because Excel dates and
times are in days, so convert from minutes to days.]

You didn't need to do the conversion the long way round converting to
decimals and back again. You could put in the 31:12 as either 0:31:12 or as
31:12.0 (but not as 31:12 because that would be treated as hh:mm, not as
mm:ss), and then just divide by 4.
--
David Biddulph

"RuSLMaN" wrote in message
...
Whenever I try to calculate a running pace (minutes/per mile) it returns a
number in decimal form. But the decimal then has to be converted to
seconds.


For example, I want to calculate my pace (minutes/mile) if I ran 4 miles
in
31 minutes and 12 seconds. To get an accurate pace, I have to convert 31
minutes and 12 seconds to a decimal format which would be equal to 31.2
minutes. (12 seconds divided by 60 seconds = .2)


Then when I divide 31.2 minutes by 4.0 miles the answer I get in Excel is
7.80.

Since there are only 60 seconds in a minute, 7.80 is actually 7 minutes
and
48 seconds per mile. (0.8 x 60 seconds) Is there a function in Excel
that
can convert decimals to minutes and seconds without having to do some
advanced programming?

Any ideas?

Thanks,
RuSL

RuSLMaN wrote:
Whenever I try to calculate a running pace (minutes/per mile) it returns a
number in decimal form. But the decimal then has to be converted to seconds.


For example, I want to calculate my pace (minutes/mile) if I ran 4 miles in
31 minutes and 12 seconds. To get an accurate pace, I have to convert 31
minutes and 12 seconds to a decimal format which would be equal to 31.2
minutes. (12 seconds divided by 60 seconds = .2)


Then when I divide 31.2 minutes by 4.0 miles the answer I get in Excel is
7.80.

Since there are only 60 seconds in a minute, 7.80 is actually 7 minutes and
48 seconds per mile. (0.8 x 60 seconds) Is there a function in Excel that
can convert decimals to minutes and seconds without having to do some
advanced programming?

Any ideas?

Thanks,
RuSL

Not quite programming, but a bit of function/formula work.

If 7.8 is in A1 then place this formula in another cell:
=INT(A1)+(A1-INT(A1))*60/100.
First part gives you 7; second part gives you 0.8, and the
multiplication/division gives you .48. You could shorten 60/100 to 0.6.
You may also want to format the cell to display only two decimal places.

Bill

But why would he want to display as 7.48 (which it isn't) in place of 7:48
(which it is)?
--
David Biddulph

"Bill Sharpe" wrote in message
...

Not quite programming, but a bit of function/formula work.

If 7.8 is in A1 then place this formula in another cell:
=INT(A1)+(A1-INT(A1))*60/100.
First part gives you 7; second part gives you 0.8, and the
multiplication/division gives you .48. You could shorten 60/100 to 0.6.
You may also want to format the cell to display only two decimal places.

Bill

RuSLMaN wrote:
Whenever I try to calculate a running pace (minutes/per mile) it returns
a number in decimal form. But the decimal then has to be converted to
seconds. For example, I want to calculate my pace (minutes/mile) if I ran
4 miles in 31 minutes and 12 seconds. To get an accurate pace, I have to
convert 31 minutes and 12 seconds to a decimal format which would be
equal to 31.2 minutes. (12 seconds divided by 60 seconds = .2) Then when
I divide 31.2 minutes by 4.0 miles the answer I get in Excel is 7.80.

Since there are only 60 seconds in a minute, 7.80 is actually 7 minutes
and 48 seconds per mile. (0.8 x 60 seconds) Is there a function in
Excel that can convert decimals to minutes and seconds without having to
do some advanced programming? Any ideas? Thanks,
RuSL

David Biddulph wrote:
But why would he want to display as 7.48 (which it isn't) in place of 7:48
(which it is)?
--

You could modify/complicate the formula to change it to text and replace
the period with a colon. However, I think that 7.48 is a big improvement
over 7.8 and understandable to the original poster. He could also split
the formula into two separate formulas and place the minutes in one
column and the seconds in the next column and put a heading at the top
of the two columns. If you really wanted to, you could use three columns
and put the colon in the middle one. <vbg

Bill

But why would you want to go through that sort of nausea to put a colon in a
text string, when Excel time format will display 7:48 as 7:48 anyway? Why
not use it the way it's intended?
--
David Biddulph

"Bill Sharpe" wrote in message
...
David Biddulph wrote:
But why would he want to display as 7.48 (which it isn't) in place of
7:48 (which it is)?
--

You could modify/complicate the formula to change it to text and replace
the period with a colon. However, I think that 7.48 is a big improvement
over 7.8 and understandable to the original poster. He could also split
the formula into two separate formulas and place the minutes in one column
and the seconds in the next column and put a heading at the top of the two
columns. If you really wanted to, you could use three columns and put the
colon in the middle one. <vbg

Bill

David Biddulph wrote:
But why would you want to go through that sort of nausea to put a colon in a
text string, when Excel time format will display 7:48 as 7:48 anyway? Why
not use it the way it's intended?
--
David Biddulph

You're right, and a much easier approach including the input you suggested.

Bill

I am trying to apply what you have for an answer but am failing miserably! My ultimate goal is to be able to provide 1/4, 1/2, 3/4 and Lap pace times around an unconventional track. To be able to enter my desired completion time and the desired split times fill in. There would be 15 quarters, not quite a complete 4th lap.

William,

In cell B1, enter the track size, in decimal miles.
In cell B2, enter the distance that you want to run.
In cell B3, enter the pace in 0:minutes:seconds that you want to run: 0:8:30 to enter 8 minutes and
30 seconds per mile.

For just lap times, in cell B4, enter this
=IF(ROW(B1)<=$B$2/$B$1,"Lap " & ROW(B1) & ": " & TEXT($B$1*ROW(B1)*$B$3,"mm:ss"),"")

Format B3 and B4 for m:ss, and then copy B4 down the row as far as you want.

Or, put this in A4:
=IF(ROW(B1)<=$B$2/$B$1,"Lap " & ROW(B1) & ": ","")
And this in B4
=IF(ROW(A1)<=$B$2/$B$1,$B$1*ROW(A1)*$B$3,"")

to split the times out so that you can do other math on them.

If you want the times at the 1/4, 1/2 and 3/4 marks around the laps, then use this in A4 (note that
the formula will probably wrap, so take out any line returns):

=IF((ROW(A1)-1 + COLUMN(A1)/4)<=$B$2/$B$1,"Lap " & ROW(A1) &": " & COLUMN(A1) & "/4: " &
TEXT($B$1*$B$3*(4*(ROW(A1)-1)+COLUMN(A1))/4,"mm:ss"),"")

and copy to B4:D4, then copy A4:D4 down for as many rows as you need.

HTH,
Bernie
MS Excel MVP


<William Dalton wrote in message ...
I am trying to apply what you have for an answer but am failing miserably! My ultimate goal is to
be able to provide 1/4, 1/2, 3/4 and Lap pace times around an unconventional track. To be able to
enter my desired completion time and the desired split times fill in. There would be 15 quarters,
not quite a complete 4th lap.

Thanks to both of you (Bill and Dave). Both ways seem to work. I had one
more question for you, if you are inputing your time, would you recommend
splitting it into 2 columns (one for minutes and one for seconds) or is there
a way to put the time in one column and include both minutes and seconds and
then use that for the pace calculation?

For example:
One column Vs Two Columns

|Time (mm:ss)| |Minutes|Seconds|
| 31:12 | | 31 | 12 |

Thanks again for any help!

Russ

"RuSLMaN" wrote:

Whenever I try to calculate a running pace (minutes/per mile) it returns a
number in decimal form. But the decimal then has to be converted to seconds.


For example, I want to calculate my pace (minutes/mile) if I ran 4 miles in
31 minutes and 12 seconds. To get an accurate pace, I have to convert 31
minutes and 12 seconds to a decimal format which would be equal to 31.2
minutes. (12 seconds divided by 60 seconds = .2)


Then when I divide 31.2 minutes by 4.0 miles the answer I get in Excel is
7.80.

Since there are only 60 seconds in a minute, 7.80 is actually 7 minutes and
48 seconds per mile. (0.8 x 60 seconds) Is there a function in Excel that
can convert decimals to minutes and seconds without having to do some
advanced programming?

Any ideas?

Thanks,
RuSL

I prefer the 31:12 so that the processing can be done more simply, but
remember that it would need to be entered as 0:31:12 or 31:12.0 to avoid it
being interpreted as 31:12:00.
--
David Biddulph

"RuSLMaN" wrote in message
...
Thanks to both of you (Bill and Dave). Both ways seem to work. I had one
more question for you, if you are inputing your time, would you recommend
splitting it into 2 columns (one for minutes and one for seconds) or is
there
a way to put the time in one column and include both minutes and seconds
and
then use that for the pace calculation?

For example:
One column Vs Two Columns

|Time (mm:ss)| |Minutes|Seconds|
| 31:12 | | 31 | 12 |

Thanks again for any help!

Russ

"RuSLMaN" wrote:

Whenever I try to calculate a running pace (minutes/per mile) it returns
a
number in decimal form. But the decimal then has to be converted to
seconds.


For example, I want to calculate my pace (minutes/mile) if I ran 4 miles
in
31 minutes and 12 seconds. To get an accurate pace, I have to convert 31
minutes and 12 seconds to a decimal format which would be equal to 31.2
minutes. (12 seconds divided by 60 seconds = .2)


Then when I divide 31.2 minutes by 4.0 miles the answer I get in Excel
is
7.80.

Since there are only 60 seconds in a minute, 7.80 is actually 7 minutes
and
48 seconds per mile. (0.8 x 60 seconds) Is there a function in Excel
that
can convert decimals to minutes and seconds without having to do some
advanced programming?

Any ideas?

Thanks,
RuSL