Explanation
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"" mins """)
This is an example of embedding text into a custom number format, and this text must be surrounded by an extra pair of double quotes. Without the extra double quotes, the custom text format looks like this:
h "hrs" m "min"
The value for days is calculated with the INT function, which simply returns the integer portion of the end date minus the start date:
INT(C5-B5) // get day value
Note: Although you can use "d" in a custom number format for days, the value will reset to zero when days is greater than 31.
Include seconds
To include seconds, you can extend the custom number format like this:
=INT(C5-B5)&" days "&TEXT(C5-B5,"h"" hrs ""m"" mins ""s"" secs""")
Total days, hours, and minutes between dates
To get the total days, hours, and minutes between a set of start and end dates, you can adapt the formula using SUMPRODUCT like this:
=INT(SUMPRODUCT(ends-starts))&" days "&TEXT(SUMPRODUCT(ends-starts),"h"" hrs ""m"" mins """)
where "ends" represents the range of end dates, and "starts" represents the range of start dates. In the example shown, D11 contains this formula:
=INT(SUMPRODUCT(C5:C9-B5:B9))&" days "&TEXT(SUMPRODUCT(C5:C9-B5:B9),"h"" hrs ""m"" mins """)
Related formulas
Get days, months, and years between dates
Calculate years between dates
Get months between dates
Related functions
INT Function
The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note that negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
TEXT Function
The Excel TEXT function returns a number formatted as text. This makes it easy to embed a formatted number (e.g., dates, times, currencies, percentages, fractions, etc.) in a text string with the number format of your choice.
