Excel 2010 Bible

Part II: Working with Formulas and Functions

268

formula assumes that cell A1 contains a date and cell A2 contains a number between 1 and 7 ( 1 for

Sunday, 2 for Monday, and so on).

=A1+A2-WEEKDAY(A1)+(A2<WEEKDAY(A1))*7

If cell A1 contains June 1, 2010 (a Tuesday), and cell A2 contains 7 (for Saturday), the formula

returns June 5, 2010. This is the first Saturday after June 1, 2010.

Determining the nth occurrence of a day of the week in a month ............................

You may need a formula to determine the date for a particular occurrence of a week day. For

example, suppose that your company payday falls on the second Friday of each month and you

need to determine the paydays for each month of the year. The following formula makes this type

of calculation:

=DATE(A1,A2,1)+A3-WEEKDAY(DATE(A1,A2,1))+

(A4-(A3>=WEEKDAY(DATE(A1,A2,1))))*7

The formula in this section assumes that

l (^) Cell A1 contains a year.
l Cell A2 contains a month.
l (^) Cell A3 contains a day number ( 1 for Sunday, 2 for Monday, and so on).
l Cell A4 contains the occurrence number (for example, 2 to select the second occurrence
of the weekday specified in cell A3).
If you use this formula to determine the date of the second Friday in November 2010, it returns
November 12, 2010.
Note
If the value in cell A4 exceeds the number of the specified day in the month, the formula returns a date from a
subsequent month. For example, if you attempt to determine the date of the fifth Friday in November 2010
(there is no such date), the formula returns the first Friday in December. n

Calculating dates of holidays ...................................................................................

Determining the date for a particular holiday can be tricky. Some, such as New Year’s Day and U.S.

Independence Day are no-brainers because they always occur on the same date. For these kinds of

holidays, you can simply use the DATE function. To enter New Year’s Day (which always falls on

January 1) for a specific year in cell A1, you can enter this function:

=DATE(A1,1,1)