How does one convert 3-4 digit numbers into a time?

[Wulf]

I have 7522 cells that have numbers in them. They're SUPPOSED to be times. I was asked if these cells (part of a larger worksheet) could be converted into times. I've been screwing with it for several hours, trying to come up with a formula, but so far nothing works the way I need it to.

I need "2200" to read as "22:00", "300" to read as "3:00", "1500" to read as "15:00" etc.

How can this be done?

 

[Richard Schollar]

Hi

Try:

=TEXT(A1,"00\:00")+0

 

[Wulf]

Hi

Try:

=TEXT(A1,"00\:00")+0

Amazing how simple things can be......

Thank you very much, that did it!

 

[joefrench]

See sheet below. Ignore the last two cells!
=CONCATENATE(REPLACE(J9,3,2,""),":",REPLACE(J9,1,2,""))
Of course, this will only work with the 4 digit values. If you want to take care of 3 digits as well, you will have to alter accordingly or add an if statement to determine the number of characters in the cell.

personal.htm
	J	K	L	M
9	2200	22:00	00	22:00:00
personal

 

[joefrench]

Oops. I guess RSchollars solutions is more efficient!