Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.

are you simply trying to put a colon in the middle of a 4 character string? – Cameron Aziz Jul 6 '12 at 6:41
Question is similar to – Kaze Jul 7 '12 at 12:08

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




share|improve this answer
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 .