Jogging Log and Elapsed time formats

[Margle]

I'm attempting to create a spreadsheet to log my jog distances, and times.
I'm wanting to be able to type in the time in a standard format (i.e. 17:38 =
17 minutes and 38 seconds), and be able to take that value from the cell
17:38 and display a pace in minutes/mile of the same format (minutes:seconds
/ mile) by dividing the displayed time 17:38 by the distance.
Example:
A2= 17:38 (min:sec)
A3=2 (miles)
A4 = A2/A3 (displayed as min:sec/mile)
I've attempted to use many of the built in time functions, but they seem to
all be based on an absolute time, and not an elapsed time. If anybody has
any ideas, I would greatly appreciate them! Thanks, and keep on pluggin' and
chuggin' those numbers!

Mike

 

[Niek Otten]

Hi Mike,

There is no differnce technically between "absolute time" and elapsed time; both are the time since a zero point.

Your formula gives me 8:49 in A3.

Note that Excel interprets 17:38 as 17 hours and 38 minutes; insignificant in your example, but it might get you into problems if
you enter times over an hour. Then use 0:17:38 format for all time entries.

If you want the format as descriptive as you post, format A3 Custom as "hh:mm:ss "/mile"


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm attempting to create a spreadsheet to log my jog distances, and times.
| I'm wanting to be able to type in the time in a standard format (i.e. 17:38 =
| 17 minutes and 38 seconds), and be able to take that value from the cell
| 17:38 and display a pace in minutes/mile of the same format (minutes:seconds
| / mile) by dividing the displayed time 17:38 by the distance.
| Example:
| A2= 17:38 (min:sec)
| A3=2 (miles)
| A4 = A2/A3 (displayed as min:sec/mile)
| I've attempted to use many of the built in time functions, but they seem to
| all be based on an absolute time, and not an elapsed time. If anybody has
| any ideas, I would greatly appreciate them! Thanks, and keep on pluggin' and
| chuggin' those numbers!
|
| Mike

 

[Niek Otten]

<"hh:mm:ss "/mile">

That should have been hh:mm:ss "/mile" between quotes. Don't enter the outer quotes in the Type box. I hope you're still
following.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Mike,
|
| There is no differnce technically between "absolute time" and elapsed time; both are the time since a zero point.
|
| Your formula gives me 8:49 in A3.
|
| Note that Excel interprets 17:38 as 17 hours and 38 minutes; insignificant in your example, but it might get you into problems
if
| you enter times over an hour. Then use 0:17:38 format for all time entries.
|
| If you want the format as descriptive as you post, format A3 Custom as "hh:mm:ss "/mile"
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| I'm attempting to create a spreadsheet to log my jog distances, and times.
|| I'm wanting to be able to type in the time in a standard format (i.e. 17:38 =
|| 17 minutes and 38 seconds), and be able to take that value from the cell
|| 17:38 and display a pace in minutes/mile of the same format (minutes:seconds
|| / mile) by dividing the displayed time 17:38 by the distance.
|| Example:
|| A2= 17:38 (min:sec)
|| A3=2 (miles)
|| A4 = A2/A3 (displayed as min:sec/mile)
|| I've attempted to use many of the built in time functions, but they seem to
|| all be based on an absolute time, and not an elapsed time. If anybody has
|| any ideas, I would greatly appreciate them! Thanks, and keep on pluggin' and
|| chuggin' those numbers!
||
|| Mike
|
|

 

[Rick Rothstein \(MVP - VB\)]

That should have been hh:mm:ss "/mile" between quotes. Don't
enter the outer quotes in the Type box. I hope you're still following.

You can also do it without the quote marks as long as you use backslashes in
front of the format's meta-characters.

hh:mm:ss /\mil\e

Rick

 

[Niek Otten]

Thanks, Rick!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|> <"hh:mm:ss "/mile">
| >
| > That should have been hh:mm:ss "/mile" between quotes. Don't
| > enter the outer quotes in the Type box. I hope you're still following.
|
| You can also do it without the quote marks as long as you use backslashes in
| front of the format's meta-characters.
|
| hh:mm:ss /\mil\e
|
| Rick
|