Show Total Time As Days, Hours, and Minutes
When you add hours in Excel, you can have the result return as total hours and minutes, but unfortunately, not as days, hours, and minutes. Displaying that will take some extra work.
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, which then allows for hours greater than 24. Although this is certainly the correct result, it means you must manually calculate howmany 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 is summing 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 [h]:mm. Assume the result of this total is 306:26:00, which, of course, represents 306 hours and 26 minutes. However, this does not 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"
Provided that cell A11 has the value of 306:26:00, the result of this formula is 12 days, 18 hours, and 26 minutes.
Select the formula result cell and then click the fx sign to the left of the Formula bar (pre-2007 and Mac, click the equals sign). 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 howmany 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). Nowyou 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 rowthat houses the total as hours can, of course, be hidden.