Software

Use an array to add time components in Excel

Use an Excel array to return a single cumulative time value. This works equally as well when the time components are single integer values instead of literal time values.

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

=SUM(TIME(hour,minute,second))

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

=SUM(firsttime:lasttime)

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:

=SUM(TIME(firsthour:lasthour,firstminute:lastminute,firstsecond:lastsecond))

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.)

may2008blog8fig1r.jpg

About Susan Harkins

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.

Editor's Picks

Free Newsletters, In your Inbox