Show Total Time as Days, Hours, and Minutes in Excel

For example, if the total time was equal to 75 hours, 45 minutes, and 00 seconds, the total generally would be displayed as 75:45:00, proving the result cell is custom-formatted as [h]:mm:ss or as 37:30:55, (see, calculate total hours) which then allows for hours greater than 24:

Although this is certainly the correct result, it means you must manually calculate how many days, hours, and minutes the total represents. This can be time-consuming and error-prone.

Assume you have a list of valid times in cells A1:A10. In cell A11, you have a regular SUM function that sums up the total hours-i.e., =SUM($A$1:$A$10). If the total of this sum is to exceed 24 hours, the result cell housing the SUM function should be formatted as 37:30:55 or a custom format of [h]:mm.

The result of this total is 306:26:00, which, of course, represents 306 hours and 26 minutes. However, this does tell you how many days/hours/minutes this total represents.

To have the result shown in days, hours, and minutes, use this formula:

=INT(A11) &" Days " & INT(MOD(A11,INT(A11))*24) & " Hours and " & MINUTE(A11) & " Minutes"

Providing that the cell A11 has the value of 306:26:00, the result of this is 12 days, 18 hours, and 26 minutes.

Let’s look at how this works. If you are not familiar with how Excel stores and uses dates and times, you should first read and understand How Date and Time Work in Excel.

Select the formula result cell (D11) and then click the fx sign to the left of the Formula bar (this is the equals sign in older and in Macintosh versions of Excel). See the following animation:

Click the first occurrence of INT from the Formula bar. This function will return the whole number 12 from the value 12.76805556. This is how many days there are.

Next, you need to determine how many hours remain after taking off 12 days. Click the second INT function from the Formula bar. Here you are using MOD(A11,INT(A11) to return the remainder of 12.76805556 divided by 12, which is 0.76805556 (the number of minutes represented as a decimal number). Now you need to multiply that by 24 (which is 18.433333) to return a number that will represent the minutes. As you want only the whole number (18), we have nested the formula MOD(A11,INT(A11))*24 into the INT function.

Click the MINUTE function from within the Formula bar. The function will return 26 from the serial number 12.76805556.

As the result returned from the MINUTE function will never be a numeric value, it is wise to at least keep the original SUM function, which returns the total as hours in a cell, so that it can be referenced and used in further calculations if needed. The row that houses the total as hours can, of course, be hidden.