Tuesday, May 31, 2005

Summing time in Excel

ExcelTip.com has this great tip.

Summing time values that are separated into hours and minutes in different columns
Problem:
Columns B:C contains numbers representing hours and minutes accordingly.
How could we sum up the numbers in both columns to a single time value?

Solution:
To get a time value representing the sum of hours and minutes in columns B:C
use the following Array Formula: {=SUM(TIME(B12:B14,C12:C14,0))}

Example:

Hours___Minutes
5_______20
6_______50
3_______10

Result: 15:20
Formula: {=SUM(TIME(B12:B14,C12:C14,0))}

Notes:
To perform an Array Formula: Insert the formula, press F2 and then press Ctrl+Shift+Enter simultaneously.
The format in the cell contains formula is:[HH:MM]


Posted by: Jethro | 10:41 PM |