Time duration with days

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.

In the example shown, the formula in cell F5 is:

=B5+TIME(C5,D5,0)

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 come from column C, minutes from column D, and seconds are hardcoded as zero. TIME returns 0.5, since 12 hours equals one half day:

TIME(12,0,0) // returns 0.5

With the number 1 in C5, we can simplify the formula to:

=1+0.5

which returns 1.5 as a final result. To display this result as total hours, a custom number format is used:

[h]:mm

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:

=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:

=B5&"d "&C5&"h "&D5&"m"

This is an example of concatenation. We are simply embedding all three numeric values into single text string, joined together with the ampersand (&) operator.

If you want to display an existing time value as a text string, you can use a formula like this:

=INT(A1)&" days "&TEXT(A1,"h"" hrs ""m"" mins """)

where A1 contains time. The INT function simply returns the integer portion of the number (days). The TEXT function is used to format hours and minutes.