Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. -- Gonzo ------------------------------------------------------------------------ Gonzo's Profile: http://www.excelforum.com/member.php...o&userid=28943 View this thread: http://www.excelforum.com/showthread...hreadid=486786 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display a number as Kilometers and k/hr & as Time
First one
Format the cell as General"K/hr" Second one use =IF(A199,INT(A1/100)&"h ","")&MOD(A1,100)&"m" -- HTH RP (remove nothere from the email address if mailing direct) "Gonzo" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display a number as Kilometers and k/hr & as Time
On Mon, 21 Nov 2005 04:35:49 -0600, Gonzo
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display a number as Kilometers and k/hr & as Time
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display a number as Kilometers and k/hr & as Time
=INT(A1/60)&"hrs "&MOD(A1,60)&"mins"
-- HTH RP (remove nothere from the email address if mailing direct) "Gonzo" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display a number as Kilometers and k/hr & as Time
"Gonzo" wrote in
message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display a number as Kilometers and k/hr & as Time
It works great guys, thanks heaps! -- Gonzo ------------------------------------------------------------------------ Gonzo's Profile: http://www.excelforum.com/member.php...o&userid=28943 View this thread: http://www.excelforum.com/showthread...hreadid=486786 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|