Convert Excel Text string to Time
Solution 1:
Assuming the value is in cell A1, use this formula:
=TIMEVALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))
What's happening is it's taking a value assume to be 1 or 2 digits for the hour and always 2 digits for the minute. For example, 1500 = 3:00 PM, 900 = 9:00 AM. You may need to format the cell as a time instead of a number.
If you have values that are earlier than 1:00 AM and your data does not have leading zeros, you'll want this more complex formula instead:
=TIMEVALUE(IF(LEN(A1)>2,LEFT(A1,LEN(A1)-2),"0")&":"&RIGHT(A1,2))
Solution 2:
In Excel 2003 these formulas worked okay until it hit a time with no hour digit (the first hour after midnight) then it produced a #VALUE! error. I added an IF statement to deal with that first hour where there were only single or double numbered minutes :-
=TIMEVALUE(IF(LEN(A1)<=2,"0:"&RIGHT(A1,2),(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))))
Solution 3:
This formula modified from Dave's and Tracy's will output the text string you want, and not require you to format the cell:
=TEXT(LEFT(A1,LEN(A1)-2) &":"& RIGHT(A1,2), "h:mm AM/PM")
If you only want hours, then change the h:mm to h.
Solution 4:
If you have the Time in such numeric way (900 -> 9am) or (0900 -> 9am) [without the colon]
There is a much more simple way to convert it to a Time format, just separate hour form minutes:
- Use function
INT(YourCell/100)as the Hour part - Use function
MOD(YourCell;100)as the Minute part - Use value
0as the Second part
Then use the function TIME to convert it to time type, so all combined would look like:
=TIME(INT(YourCell/100);MOD(YourCell;100);0)
Now you only need to give it the Time format you want (1:30 PM on the list for Time).
The trick is that if you divide by one hundred and take the integer part you will loose the minute part, so you will have the "Hours", by other part if you take the module of dividing by one hundred you will get the minute part losing the hour part... then you only need to combine them.
Solution 5:
The answer is much simpler. I had the same problem.
Use custom format 00\:00 on the input cell.
To turn that cell into an actual time to use in calculations use=TIME(TRUNC(A1/100),MOD(A1,100),0)
Works perfectly for me ;)
source: http://www.pcmag.com/article2/0,2817,2316755,00.asp