You can use the following formulas in Excel to convert text to a time format:
Formula 1: Convert Hours & Minutes Text to Time
=--TEXT(A2,"00\:00")
This particular formula will convert the text in cell A2 to a time format with hours and minutes.
For example, if cell A2 contains 0455 then this formula will return 4:55 AM.
Formula 2: Convert Hours, Minutes & Seconds Text to Time
=--TEXT(A2,"00\:00\:00")
This particular formula will convert the text in cell A2 to a time format with hours, minutes and seconds.
For example, if cell A2 contains 045522 then this formula will return 4:55:22 AM.
The following examples show how to use each formula in practice.
Example 1: Convert Hours & Minutes Text to Time
Suppose we have the following column of text values that represent time values in Excel:
Suppose we would like to format the text as time with hours and minutes.
To do so, we can type the following formula into cell B2:
=--TEXT(A2,"00\:00")
We can then click and drag this formula down to each remaining cell in column B:
By default, the time values will be displayed as decimals in Excel.
To format these decimals in a time format, highlight the cell range B2:B10, then type Ctrl + 1 to bring up the Format Cells dialog box.
Then click Time from the Category box, then click the format shown as 1:30 PM:
Once you click OK, each of the decimals will be formatted as times with hours, minutes and either AM or PM:
Each of the text values in column A are now shown as time values in column B.
Example 2: Convert Hours, Minutes & Seconds Text to Time
Suppose we have the following column of text values that represent time values in Excel:
Suppose we would like to format the text as time with hours and minutes.
To do so, we can type the following formula into cell B2:
=--TEXT(A2,"00\:00\:00")
We can then click and drag this formula down to each remaining cell in column B:
By default, the time values will be displayed as decimals.
To format these decimals in a time format, highlight the cell range B2:B10, then type Ctrl + 1 to bring up the Format Cells dialog box.
Then click Time from the Category box, then click the format shown as 1:30:55 PM:
Once you click OK, each of the decimals will be formatted as times with hours, minutes and seconds with either AM or PM:
Each of the text values in column A are now shown as time values in column B.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Subtract Minutes from Time in Excel
How to Format Time with Milliseconds in Excel
How to Convert Decimal Time to Hours & Minutes in Excel
