Custom Cell Time Format

[Minitman]

Greetings,

I am looking for a custom cell format. I want to type in a number
with or without a decimal and have it show as hours and/or minutes.

Anyone know how this done?

Any help would be appreciated.

-Minitman

 

[Pete_UK]

A format won't change the value in a cell, so you have to do something
to make Excel interpret your number as a time, although I'm a bit
confused how you would expect Excel to decide if what has been typed
in represents hours and/or minutes.

One way is to set up Auto-correct on the Tools menu so that it changes
a full-stop into a colon, so that if you type 1.24 for example, this
will be changed to 1:24 and Excel will interpret this as 1 hour and 24
minutes. Similarly, 0.36 will be interpreted as 36 minutes. This is
handy for rapid data entry via the numeric keypad, but don't forget to
delete that entry in Autocorrect once you have finished.

Hope this helps.

Pete

 

[RagDyer]

A nice option when using auto-correct is to use 2 decimal points (periods -
dots) when you want a colon to display.
1..24 = 1:24
It's just as easy to enter them from the num keypad, and perhaps you may not
even have to remove it when you're done.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
A format won't change the value in a cell, so you have to do something
to make Excel interpret your number as a time, although I'm a bit
confused how you would expect Excel to decide if what has been typed
in represents hours and/or minutes.

One way is to set up Auto-correct on the Tools menu so that it changes
a full-stop into a colon, so that if you type 1.24 for example, this
will be changed to 1:24 and Excel will interpret this as 1 hour and 24
minutes. Similarly, 0.36 will be interpreted as 36 minutes. This is
handy for rapid data entry via the numeric keypad, but don't forget to
delete that entry in Autocorrect once you have finished.

Hope this helps.

Pete

 

[Minitman]

Thanks guys.

Pete that was a very good suggestion. And you are right, I did not
know how to tell excel that these numbers where hours and minutes.
Unfortunately, I could not use your suggestion it as it was. This is
in a data entry situation and there are too many other entries that
use the "." as a decimal.

RagDyer your work around to Pete's idea did the trick.

You both have my heart felt thanks for your help.

-Minitman

 

[Ragdyer]

We appreciate the feed-back.