Display a number as Kilometers and k/hr & as Time

Hi Guys,
Can someone help me display a number from a formular as kimometers and kilimoters per hour, like "21k/hr" and also display 2 digit numbers at time, as in 34 would show as 0:34 or even better would be as " 34 mins" in the cell and 3 to 4 digit numbers as time like "3:45" or "3h 45m" would be great.

Thanks guys, other than that, if you have any ideas to help me please look at http://users.bigpond.net.au/adams_st...ng%20stats.xls and let me know if i can get rid of decimal time in the data sheet..


Thanks again.

First one

Format the cell as General"K/hr"

Second one

use =IF(A1>99,INT(A1/100)&"h ","")&MOD(A1,100)&"m"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gonzo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Guys,
> Can someone help me display a number from a formular as kimometers
> and kilimoters per hour, like "21k/hr" and also display 2 digit numbers
> at time, as in 34 would show as 0:34 or even better would be as " 34
> mins" in the cell and 3 to 4 digit numbers as time like "3:45" or "3h
> 45m" would be great.
>
> Thanks guys, other than that, if you have any ideas to help me please
> look at http://users.bigpond.net.au/adams_st...ng%20stats.xls and
> let me know if i can get rid of decimal time in the data sheet..
>
>
> Thanks again.
>
>
> --
> Gonzo
> ------------------------------------------------------------------------
> Gonzo's Profile:
http://www.excelforum.com/member.php...o&userid=28943
> View this thread: http://www.excelforum.com/showthread...hreadid=486786
>

On Mon, 21 Nov 2005 04:35:49 -0600, Gonzo
<[email protected]> wrote:

>
>Hi Guys,
>Can someone help me display a number from a formular as kimometers
>and kilimoters per hour, like "21k/hr" and also display 2 digit numbers
>at time, as in 34 would show as 0:34 or even better would be as " 34
>mins" in the cell and 3 to 4 digit numbers as time like "3:45" or "3h
>45m" would be great.
>
>Thanks guys, other than that, if you have any ideas to help me please
>look at http://users.bigpond.net.au/adams_st...ng%20stats.xls and
>let me know if i can get rid of decimal time in the data sheet..
>
>
>Thanks again.

Assuming you've computed your km and km/hr as decimal numbers:

1. Select the Cell
2. Format/Cells/Number/Custom Type:
0 "k"
or
0 "k/hr"

For the time function, because of the way Excel stores times, you need to first
divide your "decimal time" by 24. This may require that you also alter your
km/hr formula to multiply by 24.

Then

Format/Cells/Number/Custom Type:
[h]"h "mm"m"


--ron

Thanks for your help, i got the k/hr thing working, pretty simple.. i should have guessed :P

But i still cant get the time to work to convert 3 digit mins to a time.. like 124 to show as " 2 hrs 04 mins"

Can you please clarify?

Again my file in question is here http://users.bigpond.net.au/adams_st...ng%20stats.xls

The password for the protection is not set.. just turn it off..

Thanks Again..

=INT(A1/60)&"hrs "&MOD(A1,60)&"mins"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gonzo" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for your help, i got the k/hr thing working, pretty simple.. i
> should have guessed :P
>
> But i still cant get the time to work to convert 3 digit mins to a
> time.. like 124 to show as " 2 hrs 04 mins"
>
> Can you please clarify?
>
> Again my file in question is here
> http://users.bigpond.net.au/adams_st...ng%20stats.xls
>
> The password for the protection is not set.. just turn it off..
>
> Thanks Again..
>
>
> --
> Gonzo
> ------------------------------------------------------------------------
> Gonzo's Profile:
http://www.excelforum.com/member.php...o&userid=28943
> View this thread: http://www.excelforum.com/showthread...hreadid=486786
>

"Gonzo" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for your help, i got the k/hr thing working, pretty simple.. i
> should have guessed :P
>
> But i still cant get the time to work to convert 3 digit mins to a
> time.. like 124 to show as " 2 hrs 04 mins"
>
> Can you please clarify?
>
> Again my file in question is here
> http://users.bigpond.net.au/adams_st...ng%20stats.xls
>
> The password for the protection is not set.. just turn it off..

As you've got the time merely as a number of minutes, divide it by (24*60)
and format as time. If you want the format as you've asked for, custom
format as:
h "hrs" mm "mins"
--
David Biddulph

It works great guys, thanks heaps!