Quick, clean, and to the point

Time duration with days

Excel formula: Time duration with days
Generic formula 

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.

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


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:


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:

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

Dave Bruns

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.