Table of Contents
Converting text to time in Excel is a simple process that allows you to convert text values into a time format that can be used for calculations or analysis. This feature is particularly useful when working with time-based data such as schedules, appointments, or project timelines.
To convert text to time in Excel, you can use the TIMEVALUE function. This function takes a text string and converts it into a time value, which can then be formatted to display in a specific time format. For example, if you have a cell with the text “9:00 AM” and apply the TIMEVALUE function, it will be converted to the time value of 9:00:00 AM, which can then be formatted as 9:00 AM.
Some examples of converting text to time in Excel include converting military time (ex: 1300) to standard time (ex: 1:00 PM), converting durations (ex: 2 hours 30 minutes) to a time format (ex: 2:30:00), and converting time zones (ex: EST) to specific times (ex: 10:00 AM EST).
In summary, converting text to time in Excel is a useful feature that allows you to work with time-based data efficiently. By using the TIMEVALUE function, you can easily convert text strings into time values and format them according to your specific needs.
Convert Text to Time in Excel (With Examples)
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: