Microsoft Office Tutorials and References

In Depth Information

**Determining the last day of a month**

Thanksgiving Day

Thanksgiving Day is celebrated on the fourth Thursday in November. This formula

calculates Thanksgiving Day for the year in cell A1:

16

=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 speciﬁ ed

month. The following formula 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 example, a day of 29 when February contains only 28 days.

The following formula returns
TRUE
if the year in cell A1 is a leap year. Otherwise, it

returns
FALSE
:

=IF(MONTH(DATE(A1,2,29))=2,TRUE,FALSE)

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

The following formula is a bit more complicated, but it correctly identiﬁ es 1900 as a

nonleap year. This formula assumes that cell A1 contains a year:

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),TRUE, FALSE)