I recently acquired a worksheet with time components entered separately. I have no idea why they did this, as doing so seems inefficient and unnecessary to me. Just the same, I had to work with it.
The first problem was getting Excel to recognize integer values as time values. Fortunately, that's not so difficult. I just used the TIME function to add the hour and minute components using the formula
In this case, there are no seconds, so I used 0 for the seconds. After combining hours and minutes for each row, it was a simple step to add the individual times using this formula
Then it hit me — I didn't need intermediate (or subtotal) formulas. An array would return the cumulative time value with just one formula. (An array formula performs multiple calculations on one or more sets of values.) The following array formula returned the same cumulative result:
Again, I used 0 for the seconds. When using the array formula, be sure to apply an appropriate time format. In my case, I applied the hh:mm:ss format. (Be sure to accommodate your regional time settings.)
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.