• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

6. Hacking Formulas and Functions > 76. Show Total Time as Days, Hours, and Min...

Show Total Time as Days, Hours, and Minutes

.

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.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint