Formula for Summing Elapsed Hours and Minutes in Excel

• 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 "hours:minutes" format that may be greater than 24 hours and also display the result as "hours:minutes." You can do this by using the formulas in the following example:

In cell A1, enter the following

   =SUM(VALUE(RIGHT(TEXT(B1:B5,"hh:mm"),2)))

In cell A2, enter the following

     =SUM(VALUE(LEFT(TEXT(B1:B5,"hh:mm"),LEN(TEXT(B1:B5,"hh:mm"))-3)))

In cell A3, enter the following

   =(A2+INT(A1/60))&":"&TEXT((A1-60*INT(A1/60)),"00")

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 "hh:mm" 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  |

            A
         ---------
        1|    173|
        2|    232|
        3|234:53 |