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

#### Wulf

##### Active Member
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?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Richard Schollar

##### MrExcel MVP
Hi

Try:

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

#### Wulf

##### Active Member
Hi

Try:

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

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

Thank you very much, that did it!

#### joefrench

##### Active Member
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
JKLM
9220022:000022:00:00
personal

#### joefrench

##### Active Member
Oops. I guess RSchollars solutions is more efficient!

Replies
1
Views
254
Replies
3
Views
136
Replies
1
Views
153
Replies
5
Views
82
Replies
3
Views
298

1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back