Simple Data

[Miss Pippa]

I would think this is a simple one but all I want to do is a simple cell format, so that if I type in that cell 0845 it appears as 08:45, in other words just auto insert the colon. All the help I seen to encounter ends up going into time calulations and stuff. I tried the custom format hh:mm and the time format 13:30:55 and both just return 00:00 to whatever I type in. Please can anyone help.

 

[Rick Rothstein]

You can use this custom format to force the colon into the displayed number...

00\:00

HOWEVER, that will not make the value a real time value... for your example, it will display 08:45 but the value in the cell will still be 845 (you do not have to type the leading zero if you don't want). A cell format only affect how the value looks... it cannot physically change the underlying value in any way (to do that, you would need VBA code).

 

[Miss Pippa]

Hey, Thanks so much Rick, I'm at work right now but managed to give it a try and it seems to work a treat. Just what I was looking for as it's just to enter data for my accounts, so not worried about the cell value.

Thanks again, Happy New Year and take care. Pippa J