Sometimes, you may have a decimal number that represents a duration of time, such as 2.5 hours or 0.75 minutes. How can you convert this decimal number to a time format that Excel can recognize and use for calculations, such as 2:30 or 0:45? In this article, we will show you how to convert decimal hours, minutes, or seconds to a time in Excel formula using different methods and examples.
Excel stores time values as fractions of a day. For example, 12:00 PM is stored as 0.5, since it is half of a day. Similarly, 6:00 AM is stored as 0.25, since it is a quarter of a day. This means that to convert a decimal number to a time value, you need to divide the decimal number by the number of hours, minutes, or seconds in a day.
- To convert a decimal number to hours, divide by 24, since there are 24 hours in a day.
- To convert a decimal number to minutes, divide by 1440, since there are 1440 minutes in a day (24 x 60).
- To convert a decimal number to seconds, divide by 86400, since there are 86400 seconds in a day (24 x 60 x 60).
After dividing the decimal number by the appropriate factor, you can apply a time format to the result to display it as hours, minutes, or seconds.
Procedures
There are different ways to convert decimal hours, minutes, or seconds to a time in Excel formula, depending on the format of your data and the desired output. Here are some common methods:
Method 1: Using the TEXT function
The TEXT function can convert a numeric value to a text value in a specified format. You can use the TEXT function to convert a decimal number to a time format by dividing the decimal number by the appropriate factor and applying a time format code as the second argument of the TEXT function.
The syntax of the TEXT function is:
=TEXT (value, format_text)
Where value is the numeric value to convert, and format_text is the text code that specifies the format of the output.
The following table shows some common time format codes that you can use with the TEXT function:
| Format code | Description | Example |
|---|---|---|
| h:mm | Hours and minutes | 2:30 |
| h:mm:ss | Hours, minutes, and seconds | 2:30:15 |
| [h]:mm | Hours and minutes, showing more than 24 hours | 26:30 |
| [h]:mm:ss | Hours, minutes, and seconds, showing more than 24 hours | 26:30:15 |
| [m] | Minutes, showing more than 60 minutes | 150 |
| [m]:ss | Minutes and seconds, showing more than 60 minutes | 150:15 |
| [s] | Seconds, showing more than 60 seconds | 900 |
For example, if you have a decimal number in cell A2 that represents hours, you can use the following formula to convert it to a time format of hours and minutes:
=TEXT (A2/24, “h:mm”)
If you have a decimal number in cell B2 that represents minutes, you can use the following formula to convert it to a time format of minutes and seconds:
=TEXT (B2/1440, “m:ss”)
If you have a decimal number in cell C2 that represents seconds, you can use the following formula to convert it to a time format of hours, minutes, and seconds:
=TEXT (C2/86400, “h:mm:ss”)
Note that the TEXT function returns a text value, not a numeric value. This means that you cannot use the result for further calculations. If you want to convert a decimal number to a time value that Excel can recognize and use for calculations, you can use the next method.
Method 2: Using the TIME function
The TIME function can create a time value based on the given hour, minute, and second components. You can use the TIME function to convert a decimal number to a time value by extracting the hour, minute, and second components from the decimal number and using them as the arguments of the TIME function.
The syntax of the TIME function is:
=TIME (hour, minute, second)
Where hour, minute, and second are the numeric values that represent the hour, minute, and second components of the time value.
To extract the hour, minute, and second components from a decimal number, you can use the following formulas:
- To extract the hour component, use the INT function to truncate the decimal number to an integer value. For example, INT (2.5) returns 2.
- To extract the minute component, use the MOD function to get the remainder of dividing the decimal number by 1, then multiply by 60 and round to the nearest integer. For example, ROUND (MOD (2.5, 1) * 60, 0) returns 30.
- To extract the second component, use the MOD function to get the remainder of dividing the decimal number by 1/24, then multiply by 3600 and round to the nearest integer. For example, ROUND (MOD (2.5, 1/24) * 3600, 0) returns 0.
For example, if you have a decimal number in cell A2 that represents hours, you can use the following formula to convert it to a time value of hours and minutes:
=TIME (INT (A2), ROUND (MOD (A2, 1) * 60, 0), 0)
If you have a decimal number in cell B2 that represents minutes, you can use the following formula to convert it to a time value of minutes and seconds:
=TIME (0, INT (B2), ROUND (MOD (B2, 1) * 60, 0))
If you have a decimal number in cell C2 that represents seconds, you can use the following formula to convert it to a time value of hours, minutes, and seconds:
=TIME (0, 0, C2)
Note that the TIME function returns a numeric value that Excel can recognize and use for calculations. You can apply a time format to the result to display it as hours, minutes, or seconds.
Explanation
To illustrate the methods of converting decimal hours, minutes, or seconds to a time in Excel formula, let’s use a sample scenario with some real data.
Suppose you have a table that shows the duration of some tasks in decimal hours, minutes, or seconds, as shown below:
| Task | Duration |
|---|---|
| A | 2.5 |
| B | 0.75 |
| C | 900 |
The duration of task A is in decimal hours, the duration of task B is in decimal minutes, and the duration of task C is in decimal seconds. You want to convert these decimal numbers to a time format that Excel can recognize and use for calculations, such as adding up the total duration of all tasks.
To do this, you can use either the TEXT function or the TIME function, depending on whether you want to convert the decimal numbers to text values or numeric values.
Using the TEXT function, you can enter the following formulas in column C to convert the decimal numbers to time format:
| Task | Duration | Time Format |
|---|---|---|
| A | 2.5 | =TEXT (A2/24, “h:mm”) |
| B | 0.75 | =TEXT (B2/1440, “m:ss”) |
| C | 900 | =TEXT (C2/86400, “h:mm:ss”) |
The results will look like this:
| Task | Duration | Time Format |
|---|---|---|
| A | 2.5 | 2:30 |
| B | 0.75 | 0:45 |
| C | 900 | 0:15:00 |
Note that the values in column C are text values, not numeric values. This means that you cannot use them for further calculations, such as adding up the total duration of all tasks. If you try to do so, you will get a #VALUE! error, as shown below:
| Task | Duration | Time Format | Total |
|---|---|---|---|
| A | 2.5 | 2:30 | |
| B | 0.75 | 0:45 | |
| C | 900 | 0:15:00 | |
| #VALUE! |
To avoid this error, you can use the TIME function instead of the TEXT function to convert the decimal numbers to time values. You can enter the following formulas in column D to convert the decimal numbers to time values:
| Task | Duration | Time Format | Time Value |
|---|---|---|---|
| A | 2.5 | 2:30 | =TIME (INT (A2), ROUND (MOD (A2, 1) * 60, 0), 0) |
| B | 0.75 | 0:45 | =TIME (0, INT (B2), ROUND (MOD (B2, 1) * 60, 0 |
The results will look like this:
| Task | Duration | Time Format | Time Value |
|---|---|---|---|
| A | 2.5 | 2:30 | 0.104167 |
| B | 0.75 | 0:45 | 0.000521 |
| C | 900 | 0:15:00 | 0.010417 |
Note that the values in column D are numeric values that Excel can recognize and use for calculations. You can apply a time format to the result to display it as hours, minutes, or seconds.
Now, you can use the SUM function to add up the total duration of all tasks in column D. You can enter the following formula in cell D6:
=SUM (D2:D4)
The result will look like this:
| Task | Duration | Time Format | Time Value | Total |
|---|---|---|---|---|
| A | 2.5 | 2:30 | 0.104167 | |
| B | 0.75 | 0:45 | 0.000521 | |
| C | 900 | 0:15:00 | 0.010417 | |
| 0.115105 |
The value in cell D6 is the total duration of all tasks in decimal hours. You can apply a time format to the result to display it as hours and minutes, such as [h]:mm. The result will look like this:
| Task | Duration | Time Format | Time Value | Total |
|---|---|---|---|---|
| A | 2.5 | 2:30 | 0.104167 | |
| B | 0.75 | 0:45 | 0.000521 | |
| C | 900 | 0:15:00 | 0.010417 | |
| 2:46 |
The value in cell D6 is the total duration of all tasks in hours and minutes, which is 2 hours and 46 minutes.
Other Approaches
There are other ways to convert decimal hours, minutes, or seconds to a time in Excel formula, such as using the TIMEVALUE function or the DATE function. However, these methods may have some limitations or drawbacks, such as:
- The TIMEVALUE function can convert a text string that represents a time to a numeric value. However, the text string must be in a valid time format, such as “2:30” or “0:45”. This means that you need to concatenate the decimal number with a colon and a zero before using the TIMEVALUE function. For example, TIMEVALUE (A2 & “:00”) can convert the decimal number in cell A2 to a time value. However, this method may not work for decimal numbers that are greater than 24, since Excel does not recognize time values that are more than 24 hours.
- The DATE function can create a date value based on the given year, month, and day components. You can use the DATE function to convert a decimal number to a date value by using 0 as the year and month arguments, and the decimal number as the day argument. For example, DATE (0, 0, A2) can convert the decimal number in cell A2 to a date value. However, this method may not work for decimal numbers that are greater than 31, since Excel does not recognize date values that have more than 31 days in a month.
Therefore, the methods using the TEXT function or the TIME function are more reliable and flexible for converting decimal hours, minutes, or seconds to a time in Excel formula.
