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
It is possible for time to be a unit of measure, rather than a time of day represented on a wall clock. As a unit of measure, 1:00:00 may represent 1 hour, or 60 minutes, or 3600 seconds, rather than the clock time of 1:00:00 AM.
Excel doesn’t seem to understand that elapsed time, or time duration, is a concept. When I apply the custom format, [h]:mm:ss to a cell, Excel reports the contents in the formula bar with AM or PM.
Is there a way to work with elapsed time instead of clock time?
In particular, I’d like to convert the text: “20:17 ” to the number: 1217. They both represent 1217 seconds. Or “1:20:17 ” to 4817.
Does Excel have formats or functions that would help with these conversions, or will I have to parse the text myself?
Hi Kenneth…In Excel, you can indeed work with elapsed time as a unit of measure instead of clock time. Excel does have a way to handle this, though it might require some formula work to convert times into the exact format you’re seeking.
### Step 1: Formatting Elapsed Time
Excel stores time as a fraction of a day. For example, `1:00:00` is stored as `1/24`, because 1 hour is one twenty-fourth of a day. When you apply the `[h]:mm:ss` format, Excel should treat it as elapsed time and display it without AM/PM.
### Step 2: Converting Time to Seconds
To convert elapsed time like `”1:20:17″` to seconds (`4817`), you can use the following approach:
1. **Text Parsing and Conversion:**
– You can split the text string and convert it manually using Excel functions.
For a time in the format `”h:mm:ss”`:
“`excel
=LEFT(A1, FIND(“:”, A1)-1)*3600 + MID(A1, FIND(“:”, A1)+1, FIND(“:”, A1, FIND(“:”, A1)+1)-FIND(“:”, A1)-1)*60 + RIGHT(A1, LEN(A1)-FIND(“:”, A1, FIND(“:”, A1)+1))
“`
For a time in the format `”mm:ss”`:
“`excel
=LEFT(A1, FIND(“:”, A1)-1)*60 + RIGHT(A1, LEN(A1)-FIND(“:”, A1))
“`
2. **Excel Functions:**
– If your time is in an actual time format (like `1:20:17`), you can simply multiply by 86400 (the number of seconds in a day) to get the total seconds:
“`excel
=A1*86400
“`
This formula works because Excel stores time as a fraction of a day. For example, `1:20:17` would represent `1 hour, 20 minutes, and 17 seconds`, which is exactly `4817` seconds.
### Example Usage:
– If `A1` contains `20:17`, the formula:
“`excel
=HOUR(A1)*3600 + MINUTE(A1)*60 + SECOND(A1)
“`
should give you `1217`.
– If `A1` contains `1:20:17`, the formula:
“`excel
=HOUR(A1)*3600 + MINUTE(A1)*60 + SECOND(A1)
“`
should give you `4817`.
Using these methods, you can easily convert elapsed time to seconds without needing to manually parse the text.