Hi Brian, 2009/3/22 Brian Barker <[email protected]>: > 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!
Seriously though convert a time value to a valid decimal value and vice versa. >> 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. Thanks for the assistance but there is something wrong. Values I get from an old sharp EL-532 Scientific calculator, which I trust absolutely are different to the ones you give, and yet I cannot see how. My values are entered so: Distance(km) Time(0.mmss) 3.2 0.3407 The calculated values are: Speedkm/hr) Min/km 5.63 0.1039 ie 10m 39s How can these values be achieved with Calc formulae? Regards Hylton --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
