[Previous] [Contents] [Next]
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 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 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 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 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 time, you should first read and understand Date and Time.
Select the formula result cell 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). 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
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.
[Previous] [Contents] [Next]