Page 283 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 240
Part II
Working with Formulas and Functions
Independence Day
This holiday always falls on July 4:
=DATE(A1,7,4)
Labor Day
Labor Day occurs on the first Monday in September. This formula calculates Labor Day for the year in cell A1:
=DATE(A1,9,1)+IF(2<WEEKDAY(DATE(A1,9,1)),7-WEEKDAY
(DATE(A1,9,1))+2,2-WEEKDAY(DATE(A1,9,1)))+((1-1)*7)
Veterans Day
This holiday always falls on November 11:
=DATE(A1,11,11)
Columbus Day
This holiday occurs on the second Monday in October. This formula calculates Columbus Day for the year
in cell A1:
=DATE(A1,10,1)+IF(2<WEEKDAY(DATE(A1,10,1)),7-WEEKDAY
(DATE(A1,10,1))+2,2-WEEKDAY(DATE(A1,10,1)))+((2-1)*7)
Thanksgiving Day
Thanksgiving Day is celebrated on the fourth Thursday in November. This formula calculates Thanksgiving
Day for the year in cell A1:
=DATE(A1,11,1)+IF(5<WEEKDAY(DATE(A1,11,1)),7-WEEKDAY
(DATE(A1,11,1))+5,5-WEEKDAY(DATE(A1,11,1)))+((4-1)*7)
Christmas Day
This holiday always falls on December 25:
=DATE(A1,12,25)
Determining the last day of a month
To determine the date that corresponds to the last day of a month, you can use the DATE function.
However, you need to increment the month by 1 and use a day value of 0. In other words, the “0th” day of
the next month is the last day of the current month.
The following formula assumes that a date is stored in cell A1. The formula returns the date that corre-
sponds to the last day of the month.
=DATE(YEAR(A1),MONTH(A1)+1,0)
You can use a variation of this formula to determine how many days comprise a specified month. The for-
mula that follows returns an integer that corresponds to the number of days in the month for the date in
cell A1:
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

240