Formatting cell

[Guest]

I need to format a colum so that a number such as 201122 would show as
20:11:22. How do I do this?

 

[Paul Corrado]

Aus, do you want the number in the cell to remain as 201122, or do you want
it changed to what appears to be a time value of 20 hours 11 minutes and 22
seconds?

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/DateTimeEntry.htm

not possible with formatting alone!

 

[Paul Corrado]

The Custom format

##":"##":"##

works in my version of XL2000

 

[Frank Kabel]

Hi
agree, but this is not a value you can easily calculate with ))

 

[Guest]

Thanks Paul.

We need military time but the numbers are imported as six numbers so all I
want to do is put the colons between each two digits.

I also tried your suggestion of ##":"##":"## but it didn't work -- possibly
because I'm using Excel 2002.

Thanks anyway.

 

[Guest]

Thanks Frank.

Looks like you've given me some excellent info, but unfortunately I'm not
technical enough to know how to use it! I'll keep a printout of your
instructions and maybe I'll find someone to help me with it (with a bit of
luck).

 

[Paul Corrado]

With your value in A1, this formula

=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

Will convert the value to time which you can format to display in military
time (among other time formats)