Excel TIMEVALUE Function – How To Use

The TIMEVALUE function is categorized as a DATE/TIME function in Excel and allows converting time values inserted or imported as a text string to valid, Excel-recognized time values.

The problem that the function solves is that it allows you to convert these time values so you can use them in subsequent calculations. Excel only recognizes time values stored as a numeric value. If your sheet has time values that are text strings, you'll need to convert them using the TIMEVALUE function before you can use them for other computations.

Businesses may find this useful, for instance, when they import hourly data from their attendance system. If the software generates data in a different file format, it may need to be imported, which could cause the hourly records to be imported as text strings.

Syntax

The syntax of the TIMEVALUE function is as follows:

Arguments:

'time_text' – The time_text argument is a required argument where you need to insert a valid text string of a time value.

Important Characteristics of the TIMEVALUE function

• The TIMEVALUE function parses the text string inserted in the time_text argument to create a native English time value.
• The time_text argument may be supplied either as a valid text string or a cell reference containing a valid text string. If the value is supplied directly into the formula, you need to enclose the text string in double-quotes.
• The TIMEVALUE function ignores dates in the supplied value.
• The supplied value must be valid, i.e., "10:11:12 PM" or "22:11:12".
• The output of the TIMEVALUE function resets to 0 every 24 hours.
• The TIMEVALUE function always returns a decimal value between 0 (which represents midnight) and 0.99988426 (which represents 23:59:59).

Examples

Let's try to see some examples of the TIMEVALUE function.

Example 1 – Plain Vanilla Formula for the TIMEVALUE Function

Plain vanillas are a great way to see what a function does as a standalone, in its purest form. A lot of functions typically have a very straightforward logic, unless we nest them or use complex operators. The TIMEVALUE function is among the simplest functions and has just one argument, where you input a valid text string.

The word valid is important because entering something like Three PM won't work. It must either be 3 PM or 15:00. To that point, here's the bare-bones version of the TIMEVALUE function.

When you enter this formula into a new spreadsheet, you'll most likely see the first output, i.e., 15:00:00, which is a valid Excel time. If you want it to say 3 PM or 3:00:00 PM, you'll need to custom format it (right-click > Formal Cells).

Example 2 – Improperly Imported Values

Sometimes, your software exports data in a disorderly format. Not only does it make your spreadsheet look like a warzone, but it also keeps you from manipulating any of that data. The TIMEVALUE function can come in handy though.

You already know that for the TIMEVALUE function to work, it needs to be supplied with a valid time in text format, correct? What happens if all components of your time are in a different column though?

Well, here's a formula you can use:

The CONCAT function is just a function that concatenates text strings. Here, what we're effectively doing is bringing the three components of the time value (i.e., the hour and minute component, and the PM) together to create a single text string that can be relayed to the TIMEVALUE function.

A baby could do this, right?

Example 3 – Using the TIMEVALUE Function with Date-Time Values

It's possible that when you import data into your Excel sheet, it imports both date and time values as a single text string. TIMEVALUE function can handle all such kinds of data and return the correct time value, provided that the date-time is recognized by Excel.

In all the cases when Excel fails to recognize the date-time string, TIMEVALUE will also render incapable to extract the time component from such dates. Here we need to handhold the TIMEVALUE function a bit and use string splitting functions like (LEFT, RIGHT, or MID) to extract the time component from the string and relay only those specific characters of the text string to the TIMEVALUE function, like so:

In the first two cases, the TIMEVALUE function on its own is able to return the time components from the date. But in the third case, since the input date is not in an Excel recognized format so the TIMEVALUE function fails to fetch the time component from it.

Notice how in the third case, the final 8 characters of the text string represent the time value (i.e., 18:23:00). You can use the RIGHT function to extract these 8 characters and relay them as-is to the TIMEVALUE function.

If your date-time text string was constructed differently, you can still use the LEFT or MID functions to pretty much extract the time component from any position, as long as it's in a valid form, of course.

Example 4 – TIMEVALUE Function on Valid Time Values

In rare cases, you may have a dataset that's largely composed of text strings that represent time values, but there are few values in between that are valid time values. When you apply the TIMEVALUE to the entire data set, you'll notice that it returns a #VALUE error for data points that are valid time values.

This isn't an anomaly, it's a shortcoming of the TIMEVALUE function. However, there's an easy fix. Instead of applying just the plain vanilla TIMEVALUE formula, use the following formula instead:

Notice how in the above example, the plain vanilla TIMEVALUE function returns a #VALUE! error, but adding the TEXT function fixes things. You can apply the longer formula even for values that are already entered as text strings, so you can just insert it in a cell and drag it down the column for the entire data set.

TIMEVALUE vs. DATEVALUE Function

The names give it away, but both functions have a largely similar role to play on your Excel sheet. Much like how the TIMEVALUE function converts valid time values inserted as text strings to an Excel time value, the DATEVALUE function does the same for dates.

Even though they have different purposes, they can do some very powerful things when combined. Let's revisit our third example for instance. We used the TIMEVALUE function to extract the time component from a date-time like text string that contained both the date and time values. However, you could've preserved that date if you had also added the DATEVALUE function to the mix, like so:

The formula here is essentially just individually calculating the date and time values and adding them together. Think about it, both functions return a numeric value (a serial number that represents date/time).

When you add the serial number for a time value (which is a fraction of a day), it adds a decimal value to the DATEVALUE function's output. When added and formatted to display both time and date values, this formula will work fine for you.

That's a wrap for the TIMEVALUE function. While you practice these formulas and work up an appetite for some more functions and formulas, we'll have another one of these functions loaded for you. We'll keep you busy—we promise.