Microsoft Office Tutorials and References

In Depth Information

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

If cell A1 contains June 1, 2013 (a Saturday) and cell A2 contains 2 (for Monday), the formula returns June 3,

2013. This is the first Monday following June 1, 2013.

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 weekday. For example, suppose

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

• Cell A1 contains a year.

• Cell A2 contains a month.

• Cell A3 contains a day number (1 for Sunday, 2 for Monday, and so on).

• Cell A4 contains the occurrence number (for example, 2 to select the second occurrence of the weekday spe-

cified in cell A3).

If you use this formula to determine the date of the second Tuesday in November 2013, it returns November 12,

2013.

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 October, 2013 (there is no such date), the formula returns the

first Friday in November.

Counting the occurrences of a day of the week

You can use the following formula to count the number of occurrences of a particular day of the week for a spe-

cified month. It assumes that cell A1 contains a date and that cell B1 contains a day number (1 for Sunday, 2 for

Monday, and so on). The formula is an array formula, so you must enter it by pressing Ctrl+Shift+Enter.

{=SUM((WEEKDAY(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT(“1:”&DAY(DATE(YEAR(A1),MONTH(A1)+1,0))))))=B1)*1)}

If cell A1 contains the date January 8, 2013 and cell B1 contains the value 3 (for Tuesday), the formula returns

5,
which reveals that January 2013 contains five Tuesdays.

The preceding array formula calculates the year and month by using the YEAR and MONTH functions. You

can simplify the formula a bit if you store the year and month in separate cells. The following formula (also an

array formula) assumes that the year appears in cell A1, the month in cell A2, and the day number in cell B1: