At 14:30 22/03/2009 +0200, Hylton Conacher wrote:
I am battling to convert time to a digital value ...
Er, everything stored in a computer is digital!
I would like to be able to enter a time value as mm:ss and then be
able to calculate what the time is for a distance over a certain
distance ie I walk 3km in 31:34, what is my speed and also my min/km?
How should I format the cells and what formula will enable me to
convert the time to a digital value that can be dived, and then
re-formatted to show a mm:ss value?
o A simple way to enter times as minutes and seconds is to prefix
them with a zero hours value. So enter your 31:34 as 0:31:34.
o Format your cell as MM:SS to see just the minutes and seconds and
suppress the (zero) hours. But be careful: any value over an hour
will then display incorrectly.
o Your actual time value is stored in the cell as a decimal value in
days. Your 31:34 is stored as 0.02192... - that fraction (a little
over a forty-sixth) of a day. If you wish to see this explicitly,
use =VALUE(xx) to show it.
o You can calculate with such values directly, as long as you
remember they are in days, not minutes; no conversion is
necessary. To calculate your speed in km/min, use
=3/(xx*24*60). The number of minutes per kilometre is the reciprocal
of this, of course: =B2*24*60/3.
o Some calculations with times result in values that are
understandably displayed as times. If you need to avoid this, apply
the VALUE() function. But you generally won't need to do this.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
