Cell Formatting

[alm395]

I don't know why I cannot remember how to do this...but...

I have a time cell and I just want to type "1753" in the cell and for it to display as "17:53". How do I have it automatically add the ":" to that cell?

 

[Sparda142]

Add 1735 to cell A1 then copy the formula into cell a2
=LEFT(a1,2)&":"&RIGHT(a1,2)

 

[ISY]

Hi

Excel custom number:

#?\:??

or

#0\:??

 

[alm395]

Add 1735 to cell A1 then copy the formula into cell a2
=LEFT(a1,2)&":"&RIGHT(a1,2)

I was hoping to have it all in one cell. Thank you though!

 

[alm395]

#?\:?? This worked!!! Thanks so much!!!

 

[Rick Rothstein]

Excel custom number:

#?\:??

or

#0\:??

Personal preference... I would use zeros instead of question marks and I would drop the leading hash sign as it does nothing for the format there. I would also note that the above format will allow non-valid values to be entered and have the colon inserted (9999 for example). With these comments in mind, perhaps this might be a better Custom Format to use...

[<=0]"Error";[<2400]0\:00;"Error"