0

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.

Community
  • 1
Thor Nowinsky
  • 9

1 Answers1

2

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
Siva Charan
  • 4,913
  • 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 06 '12 at 10:50