Page 284 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 241
Working with Dates and Times
Determining whether a year is a leap year
To determine whether a particular year is a leap year, you can write a formula that determines whether the
29th day of February occurs in February or March. You can take advantage of the fact that Excel’s DATE
function adjusts the result when you supply an invalid argument — for example, a day of 29 when February
contains only 28 days.
The following formula returns TRUE if the year of the date in cell A1 is a leap year. Otherwise, it returns
FALSE.
=IF(MONTH(DATE(YEAR(A1),2,29))=2,TRUE,FALSE)
This function returns the wrong result (TRUE) if the year is 1900. See “Excel’s leap year bug,”
CAUTION
CAUTION
earlier in this chapter.
Determining a date’s quarter
For financial reports, you may find it useful to present information in terms of quarters. The following for-
mula returns an integer between 1 and 4 that corresponds to the calendar quarter for the date in cell A1:
=ROUNDUP(MONTH(A1)/3,0)
This formula divides the month number by 3 and then rounds up the result. 13
Time-Related Functions
Excel also includes a number of functions that enable you to work with time values in your formulas. This
section contains examples that demonstrate the use of these functions.
Table 13.5 summarizes the time-related functions available in Excel. When you use the Insert Function dia-
log box, these functions appear in the Date & Time function category.
TABLE 13.5
Time-Related Functions
Function Description
HOUR Converts a serial number to an hour
MINUTE Converts a serial number to a minute
MONTH Converts a serial number to a month
NOW Returns the serial number of the current date and time
SECOND Converts a serial number to a second
TIME Returns the serial number of a particular time
TIMEVALUE Converts a time in the form of text to a serial number

241