Format Time As You Type - MS Excel

[bnc123]

I have formatted certain cells of my spreadsheet to indicate time in the format hh:mm using the custom cell formattihg option.

With this method, everytime I enter a time say 19:30, I have to insert a colon using the shift key and all.

Is there a way by which I can simply type in the four digits 1930 and the end result will show up as 19:30 in the relevant cell?

I am a layperson and know nothing about VBA (Visual Basic). So if somebody is going to suggest a VBA code, please use kids' gloves.

I will also paste this posting in the VBA forum.

Thanks.

 

[Loomah]

See your post in the VBA forum. I've given you a link which may be of use but having only just noticed your comments on VBA here you may well need to come back for further help.

Please feel free to do so but only use the one thread where I haven't made comment about duplicate threads, as that one may be lost today!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Hi,

FIRST, you need to understand Dates & Times in Excel -- Why do Dates and Times seem to be so much trouble? faq68-5827.

So knowing that, Dates & Times are just numbers that can be formatted to display a variety of different formats.

How is Excel to know that YOU want 1930 to be 19:30 which is really 0.8125 while Jack wants it to be Year 1930, which, as a date is 10959, while Jill just wants 1930?

The only way for you is to enter all your values and then use code to convert (=Time(Left(YourValue,2),Right(YourValue,2)).


Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]