Page 239 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

Manipulating Dates and Times

As with the number and text functions described earlier in this chapter, manipulating dates
and times is more in the realm of the Excel formula than something that, strictly speaking,
you need VBA to accomplish. However, it’s important to be able to work with dates and times
when you’re doing advanced work in Excel, so it seems right to include these functions.

Time and Date Serial Numbers
If you’ve ever typed a date into a worksheet cell and then changed the cell’s format to General
or Number, you’ve seen the date change into a number. For example, if you type the date
October 6, 2003 into a cell and then change the cell’s format to General, the cell’s value
changes to 37900. The reason for the change in value is that Excel treats dates and times as
numbers, not strings. In Excel, the default behavior for the program is to begin counting
from January 1, 1900. So January 1, 1900, was day 1, January 2, 1900, was day 2, and so on.
Representing a date as a number makes it easy to determine the number of days between two
events. For example, if a company were founded on August 2, 1998, and first became profit-
able on January 9, 2004, you would know the number of days it took to attain profitability
was 1986. Chapter 9

Important Dates prior to January 1, 1900, are treated as strings. You can’t manipu­
late them using the date functions covered in the next section, but they are accepted as
valid input.

Date and Time Functions

There are many instances when the date or time of an event would be important to you. If
you use Excel to track orders placed with your business, you can enter the date and time of an
order into your worksheet using the following VBA code:

ActiveCell.Value = Now
Yes, you could also enter the current date and time into a worksheet cell using the Excel for­
mula =NOW, which returns the current date and time, but if you didn’t take the time to
replace the formula with its value by clicking the Copy button, clicking the Paste Options
button (the arrow next to the button), and then selecting Values, Excel will recalculate the
formula whenever you open the workbook and replace the time of the order with the current
time. You can avoid that problem by creating a macro that assigns the value of Now to the
active cell.

213
Part 4 Advanced VBA