Page 243 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

Inside Out

Working with Times That Exceed 24 Hours
If you’ve ever tried to add two times together in an Excel worksheet, you’ve probably found
that the program doesn’t handle results of more than 24 hours gracefully. In fact, if you
were to add 8:00 (8 hours), 7:00 (7 hours), and 10:00 (10 hours) together, the worksheet
cell with the formula displays 1:00 (1 hour)! In other words, Excel disregards the first 24
hours and just shows the number of hours beyond the first 24. The same thing happens if
you add two instances of 12:00 (12 hours) to the formula—even though the total number
of hours is 49:00, the worksheet cell displays 1:00.

You can overcome this difficulty by formatting the cell in which you want to display the
results with one of the custom data formats available in the Format Cell dialog box, which
you open by clicking Format, Cells. Within the Format Cell dialog box, click the Custom
category and scroll down until you see this time format: [h]:mm:ss.
The square brackets ([]) around the hour symbol mean that the normal limit of 24 hours no
longer applies, so the cell will display time increments such as 25:00 correctly. You can do
the same thing if you want to display more than 60 minutes ([mm]:ss) or more than 60 Chapter 9
seconds ([ss]), although you’d have to create your own custom format by typing it into the
Type box. Do remember that Excel won’t let you create a format such as [h]:[mm]:ss—
because there’s no limit on the number of minutes, the number of hours would always be
0. Also, you should be aware that you can’t put square brackets around a day indicator or
a month indicator.

In this chapter, you’ve learned how to manipulate the data in your Excel workbooks. Whether
you want to summarize the data using mathematical functions, concatenate the values from
two or more cells to create detailed message boxes, or work with dates and times effectively,
you can find those functions in Excel VBA. And if the functions aren’t available in Excel VBA,
there’s a good chance you can call them from the main Excel program using the Application
object’s WorksheetFunction property.

217
Part 4 Advanced VBA