Microsoft KB Archive/66880

{| = Formula for Summing Elapsed Hours and Minutes in Excel =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q66880 The information in this article applies to:
 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
 * Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0

SUMMARY
You may want to sum a range of cells containing time values in an &quot;hours:minutes&quot; format that may be greater than 24 hours and also display the result as &quot;hours:minutes.&quot; You can do this by using the formulas in the following example:

In cell A1, enter the following

=SUM(VALUE(RIGHT(TEXT(B1:B5,&quot;hh:mm&quot;),2))) and use CTRL+SHIFT+ENTER to enter it as an array formula. This returns the total minutes from the &quot;minutes&quot; portion of your range (B1:B5). In cell A2, enter the following

=SUM(VALUE(LEFT(TEXT(B1:B5,&quot;hh:mm&quot;),LEN(TEXT(B1:B5,&quot;hh:mm&quot;))-3))) and use CTRL+SHIFT+ENTER to enter it as an array formula. This returns the total hours from the &quot;hours&quot; portion of your range. In cell A3, enter the following

=(A2+INT(A1/60))&&quot;:&quot;&TEXT((A1-60*INT(A1/60)),&quot;00&quot;) This returns the total as &quot;hours:minutes.&quot; These formulas work if the values in your range (B1:B5 in this example) are entered as either time or text values, and formatted using the &quot;hh:mm&quot; number format. This works well if you are entering hh:mm values, where an individual entry may be less than 24 hours (such as 2:30), which Excel interprets as a numeric value, or greater than 24 hours (such as 82:45), which Excel would interpret as a text value.

If the range in the example contained the following entries

B        - 1| 12:30|        2|145:23 |        3|   3:20|        4|   0:45|        5|72:55  | the formulas would return the following: A        - 1|   173|        2|    232|        3|234:53 |
 * }