Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
    
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 superuser.com/questions/51887/convert-excel-text-string-to-time –  Ellesa Jul 7 '12 at 12:08
add comment

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.

1 Answer

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
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
add comment

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