Page 191 - Excel Progamming Weekend Crash Course

k540629 ch13.qxd 9/2/03 9:34 AM Page 166

166 Saturday Afternoon

The worksheet is rather plain in appearance. Some formatting would result in a
more appealing product.
The loan amount should display as a currency amount, and the interest rate as a
percentage.
A careless user could erase or change labels or the worksheet’s formula.
I will revise this project in later sessions. After I have covered the relevant programming
topics, these shortcomings will be addressed.

Date and Time Functions

Excel maintains dates and times using the same approach that VBA does for a type Date vari-
able — a floating-point number in which the integer part represents the date, as the number
of days since December 31, 1899, and the fractional part represents the time as a fraction of
the 24-hour day. The earliest date that Excel can understand is January 1, 1900, which has
the serial number 1. When you work in a worksheet, you can enter dates in any commonly
used format, and Excel recognizes it as a date. It is converted to the corresponding serial
number for storage, but displayed in a readable date format. The same is true of times.

Unlike VBA, Excel cannot use negative date serial numbers to represent
dates before January 1, 1899.
Tip
Excel’s date and time functions parallel those found in VBA to some extent. They are
listed with brief descriptions in Table 13-1. Refer to the online help for more details.

Table 13-1 Excel’s Date and Time Functions
Function Description
Date Converts year, month, and day data into a date serial number.
DateValue Converts a string containing a formatted date into a date serial number.

Day Returns the day of the month (1–31) for a specified date.
Hour Returns the hour of the day (0–23) for a specified time.

Minute Returns the minute of the hour (0–59) for a specified time.
Month Returns the month of the year (1–12) for a specified date.
Now Returns the current date and time formatted as a date/time.
Second Returns the second of the minute (0–59) for a specified time.

Time Converts an hour, minute, and second data into a time value.
TimeValue Converts a string containing a formatted time into a time value.
Today Returns the current date formatted as a date.