How can I display a text value in time format.

[Jogi]

I have a worksheet where I update a column with time. It's set up as a text - so I can enter 1800 iso 18:00 - to save time. Is there a way I can enter time without having to enter the colon or is there a formula which will convert this for me.

Thanks for any replies!

 

[RichardS]

Assuming your time is always entered as four characters of text, eg 0600 for 6:00 AM, you could enter this in an adjacent cell

=(LEFT(A1,2)&":"&RIGHT(A1,2))+0

and format as time.

HTH

Richard

 

[PaddyD]

see also the

"Entry of Military Time without separations"

entry on

http://www.mvps.org/dmcritchie/excel/datetime.htm

paddy

 

[Jogi]

Thanks very much, Richard.

It works like a charm!

 

[Jogi]

Thanks Paddy!

I am learning a lot from this Board!!

 

[maxflia10]

I've used something similar. I formatted the cells to 00:00, then the user may enter times as 1000 and get 10:00. In another cell, I convert that text to time

=(LEFT(REPT("0",4-LEN(a1))&a1,2)&":"&RIGHT(a1,2))+0

 

[Beh162]

I'm actually curious on how to use this with my sheet because it's easier to type 1340 rather than 13:40 when you have to do it 40 times lol. I have a block of about 40 cells that I key times in. Where could I enter the formula in?