How to convert text to time in Excel

Text with a colon: TIMEVALUE or two minus signs

The sheet has times from different systems, all stored as text. You can tell because they sit on the left of the cell, =ISTEXT(B2) returns TRUE, and =SUM(B2:B3) returns 0. A booking form sent "8:30 PM". TIMEVALUE reads text that looks like a time and returns the real time behind it.

The result first shows as 0.854 or similar. That is correct: Excel stores times as fractions of a day, and 8:30 PM is 0.854 of the way through it. Format the cell with Home > Number Format > Time, or a custom format like h:mm AM/PM or hh:mm (which shows 20:30).

=--B2 (two minus signs) does the same conversion and is shorter. TIMEVALUE also handles seconds: the call system's "14:05:30" converts to 14:05:30. Once converted, the times work in any formula: =HOUR(TIMEVALUE(B2)) gives 20.

No colon: 0830 and 1745

Badge readers and military-time exports often write 0830 or 1745 with no colon. TIMEVALUE(B3) returns #VALUE!, because Excel does not see a time in it.

Split the digits yourself. Dividing by 100 and taking the INT gives the hours (8), and MOD(B3,100) gives the minutes (30). The same formula works for the text "0830", for the number 1745 (5:45 PM), and for a time that lost its leading zero in a CSV, such as 830.

If you prefer, =--TEXT(B4,"00\:00") turns 1745 into the text 17:45 and then into a real time, but the TIME version is easier to read and to check.

"1h 30m" and "45m": pull the numbers out

Task logs and project tools like to write durations as words. TIMEVALUE("1h 30m") returns #VALUE!. In Excel 365, TEXTBEFORE takes what comes before the h (the hours), and TEXTBEFORE(TEXTAFTER(B5,"h"),"m") takes the number between the h and the m (the minutes). TIME puts them together.

"45m" has no h at all, so the plain version fails with #N/A. The formula below handles both: IFERROR returns 0 hours when there is no h, and TEXTAFTER's last argument says "if there is no h, use the whole text". It returns 1:30 for B5 and 0:45 for B11; "2h 5m" in B6 gives 2:05.

Format the result as h:mm for a duration. Once they are real times you can add them: 1:30 plus 2:05 is 3:35.

"8.30" with a dot, and 8.29 as a number

A timesheet with "8.30" means 8:30, but Excel reads it as the number 8.3: =--B7 returns 8.3, not a time. Swap the dot for a colon first: =--SUBSTITUTE(B7,".",":") returns 8:30 AM.

If the dotted times arrived as numbers (B9 holds 8.29, meaning 8:29), split on the decimal point instead and round the minutes. MOD(B9,1)*100 is 28.999... because of how computers store decimals, and TIME drops the fraction, so =TIME(INT(B9),MOD(B9,1)*100,0) says 8:28. With ROUND around the minutes, as below, it says 8:29.

Don't confuse this with decimal hours. 8.29 hours is 8 hours and about 17 minutes, which =B9/24 gives. Ask where the data came from before you pick a formula.

Durations over 24 hours

TIMEVALUE only returns a time of day, a value from 0 up to (but not including) 1, so it is the wrong tool for an overtime total like "26:15". TIME also wraps around at 24 hours: =TIME(26,15,0) is 2:15 AM.

Build the duration from its parts instead. Hours divided by 24 plus minutes divided by 1440 (the minutes in a day) gives 1.09375 days for 26:15. Format the cell with the custom format [h]:mm, where the square brackets let the hours run past 24.

No formula: Text to Columns

For a column of text times with a colon, Data > Text to Columns converts them in place, without a helper column. It does not help with 0830 or "1h 30m"; use the formulas above for those.

1. Select the column of text times.
2. Go to Data > Text to Columns.
3. Choose Delimited, click Next, clear every delimiter, and click Next.
4. Leave the column format as General and click Finish. Excel re-reads each value as if you had typed it.
5. Format the column as a time (Home > Number Format > Time).