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

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.


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")


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




3:00 PM

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



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




  • 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

Not the answer you're looking for? Browse other questions tagged or ask your own question.