To enter a time duration like 2 days 6 hours and 30 minutes into Excel, you can enter days separately as a decimal value, then add the time. In the example shown, the formula in cell F5, copied down, is:
In the example shown, the goal is to enter a valid time based on days, hours, and minutes, then display the result as total hours.
The key is to understand that time in Excel is just a number. 1 day = 24 hours, and 1 hour = 0.0412 (1/24). That means 12 hours = 0.5, 6 hours = 0.25, and so on. Because time is just a number, you can add time to days and display the result using a custom number format, or with your own formula, as explained below.
On the right side of the formula, the TIME function is used to assemble a valid time from its component parts (hours, minutes, seconds). Hours comes from column C, minutes from column D, and seconds are hardcoded as zero. TIME returns 0.5, since 12 hours equals one half day:
With the number 1 in C5, we can simplify the formula to:
which returns 1.5 as a final result. To display this result as total hours, a custom number format is used:
The square brackets tell Excel to display hours over 24, since by default Excel will reset to zero at each 24 hour interval (like a clock). The result is a time like "36:00", since 1.5 is a day and a half, or 36 hours.
The formula in G5 simply points back to F5:
The custom number format used to display a result like "1d 12h 0m" is:
d"d" h"h" m"m"
More than 31 days
Using "d" to display days in a custom number format works fine up to 31 days. However, after 31 days, Excel will reset days to zero. This does not affect hours, which will continue to display properly with the number format [h].
Unfortunately, a custom number format like [d] is not supported. However, in this example, since days, hours, and minutes are already broken out separately, you can write your own formula to display days, minutes, and hours like this:
Times in Excel are factional values of 24 hours. One hour of time is 1/24, and 1 minute of time is 1/(24*60) = 1/1440. As a result, if you have a decimal value for 6 hours, and a time in A1, you can add 6 hours of time to the value in A1 like this...
Times in Excel are factional values of 24 hours. One hour of time is 1/24, and 1 minute of time is 1/(24*60) = 1/1440. As a result, if you have a decimal value for 15 minutes, and a time in A1, you can add 15 minutes of time to the value in A1 like...
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Minutes Value Time 1 1/24 60 0.04167 1:00 3 3/24 180 0.125 3:00 6 6/24 360 0.25 6:00 4 4/24 240 0.167 4:...
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Value Time 1 1/24 0.04167 1:00 3 3/24 0.125 3:00 6 6/24 0.25 6:00 4 4/24 0.167 4:00 8 8/24 0.333 8:00 12...
The complexity of calculating the number of hours between two times stems from times that cross midnight. This is because times that cross midnight often have a start time that is later than the end time (i.e. start at 9:00 PM, end at 6:00 AM). This...
Most of the work in this formula is done by the TEXT function, which applies a custom number format for hours and minutes to a value created by subtracting the start date from the end date. TEXT ( C5 - B5 , "h" " hrs " "m...
The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.