# Converting number string to a time format: hh:mm [duplicate]

Possible Duplicate:
Convert Excel Text string to Time

I must enter appointment times in a workbook.

For instance I have cell `E2` to input the time. I'll input, lets say `1500`. How can I change `1500` to `15:00`.

I have tried several `=TIMEVALUE()` statements I have found but when I put in the string `1500` or similar, the result that appears in the cell is `0:00` and the formula bar shows current date and time.

## marked as duplicate by random♦Jul 7 '12 at 16:43

Lets take CELL `A1` contains `1500`. To achieve the result, there are many ways to do.

Lets go with one-by-one.

Now you can do this way, to achieve one type of result:-

``````=TEXT(REPLACE(TEXT(A1,"##-##"),3,1,":"),"h:mm AM/PM")
``````

OR

``````=TEXT(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2), "h:mm AM/PM")
``````

OR

``````=TIME(LEFT(A1,2),RIGHT(A1,2),0)
``````

## OUTPUT:

``````3:00 PM
``````

If you want only adding a colon in between, then do this way:-

``````=LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)
``````

OR

``````=TEXT(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2), "h:mm")
``````

OR

``````=TEXT(A1,"##\:##")
``````

## OUTPUT 2:

``````15:00
``````
• Nice answer. However, re the output as `3:00 PM`, this of course can be controlled simply by formatting the cell with the appropriate time format given your 3rd option in particular. – Julian Knight Jul 6 '12 at 10:50