Table of Contents
The ability to convert text to time in Excel is an invaluable skill for anyone who uses the program regularly. It allows users to quickly and accurately convert a variety of text formats into time formats, which can be used for calculations or to create custom formats. Not only does this save time and frustration, but it can also help users to more accurately interpret data. In this article, we will discuss how to convert text to time in Excel, along with some examples of how to do so.
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.