Use the TIMEVALUE function to convert text to time in Excel
If you have a spreadsheet with time values that have been added to the spreadsheet as text values, you need the TIMEVALUE function. This will allow you to convert the text values into valid time values.
The TIMEVALUE function is used to convert a text value into a time value. It has the following syntax:
The TEXTVALUE Function - worked example
To use this function, you need a cell that contains a text value which has been entered to look like a time. An example might be "12:13 PM", "17:57" or "8:27:33 AM". You are most likely to encounter this in a spreadsheet that has been imported from another format, such as a CSV or text file. If you try typing values like this directly into Excel, you'll find that Excel recognizes them as time values and automatically converts them for you.
In the following screenshot, you'll see the TIMEVALUE function in action for the two examples given above:
Note that row 6 contains the actual result of the TIMEVALUE formula. When Excel converts the text to a time value, it doesn't automatically apply a format to the result - it simply stores the number that your time value represents.
What you're seeing in the Result row is the time in B4 expressed as a fraction of 1 day. For example, if you multiply the value in B4 by 24, you'll get 17.95, i.e. 17.95 hours out of a total of 24.
Formatting the Date Result
In many scenarios, you'll want to format the result of your TIMEVALUE formula so it displays as an actual time. Rows 7 and 8 show a couple of examples of how this number can be formatted to suit your needs.
Row 7 has the default Time format applied from the Number format list in the ribbon toolbar. This gives us the time value expressed in hours, minutes and seconds (hh:mm:ss).
Row 8 uses a custom time format that excludes the seconds value from the display:
- Choose Custom from the Number format list
- You can then specify the exact format you want as shown here:
- As you can see, the Custom list contains a number of different custom time format options (as well as other custom formats). I didn't see the one I wanted, so I simply edited an existing format using hh:mm.
- It is very important to note that applying a time format to a number does not change the number itself. This matters if you decide to perform any calculations using the time values.
Troubleshooting the TIMEVALUE function
As noted earlier, the TIMEVALUE function only works on text values that look like standard Excel formats.
In some cases, your TIMEVALUE formulas will return a #VALUE result, indicating that Excel didn't recognise the text as a valid value. This can be frustrating, especially if you can't see any problems with the values you are supplying.
Here are two examples of time values that Excel doesn't recognise:
- 8.28 am (Excel doesn't like the period/point between the 8 and 2.
- 8:28am (Excel doesn't like the fact there is no space before "am")
Another reason your TIMEVALUE formula might return a #VALUE error is that Excel is already storing the value as a time value. Try applying a different time format to the cell to see if this is the case.
If you are having a problem with a specific time format that isn't on this list, please let us know through the comments so we can solve it for you, and add it to this list. You could also read our lesson on converting text into dates, which has some additional troubleshooting tricks you could try.
To solve these specific examples, you can use the SUBSTITUTE function as part of your TIMEVALUE formulas to successfully convert the text into time values. The SUBSTITUTE function takes all instances of a text string and replaces it with another as shown here:
As you can see, the formula in B11 replaces the point with a colon, which fixes the text in B10 so it can be converted successfully.
The formula in C11 is a bit more tricky. It uses the SUBSTITUTE function to replace "am" with " am" (note the addition of the space in front of am), so Excel will recognise the text as a valid value.
In this lesson, we looked at how the TIMEVALUE function can be used to convert a text value into a time value. It requires that the text value look like a valid time that Excel will recognise. We looked as some examples of text values that Excel recognised, and then identified some that Excel doesn't - and how to fix them.
If you have any questions or suggestions regarding this lesson, feel free to use the comments section below to let us know.