Excel 2010 Bible

Chapter 12: Working with Dates and Times

271

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

corresponds 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 are in a specified month. The

formula 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))

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 the Excel DATE function adjusts the result when you supply an invalid argument — for exam-

ple, 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)

Caution
This function returns the wrong result (TRUE) if the year is 1900. See “Excel’s leap year bug,” earlier in this
chapter. n