Microsoft Office Tutorials and References

In Depth Information

**Determining the first day of the week after a date**

Determining the i rst day of the week after a date

This formula returns the speciﬁ ed day of the week that occurs after a particular date. For

example, use this formula to determine the date of the ﬁ rst Monday after a particular date.

The 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, 2013
(a Saturday), and cell A2 contains
2
(for Monday), the

formula returns
June 3, 2013
. This is the ﬁ rst 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 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

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 speciﬁ ed in cell A3).

■

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

returns
November 8,2013
.

If the value in cell A4 exceeds the number of the specii ed day in the month, the formula returns a date from a

subsequent month. For example, if you attempt to determine the date of the i fth Friday in October 2013 (there is no such

date), the formula returns the i rst Friday in November.

Calculating dates of holidays

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

and Independence Day in the United States 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 speciﬁ c year in cell A1, you can enter this function:

=DATE(A1,1,1)