Get nth day of week in month

Get nth day of week in month

Generic formula 

=date-DAY(date)+1+n*7-WEEKDAY(date-DAY(date)+8-dow)

Related formulas 

Get last working day in month

Get last weekday in month

Explanation

To get the nth day if week in a month (i.e. the first Tuesday, third Tuesday, forth Thursday, etc.) you can use a formula based on the DAY and WEEKDAY functions.

In the example shown, the formula in E5 is:

=B5-DAY(B5)+1+D5*7-WEEKDAY(B5-DAY(B5)+8-C5)

How this formula works

First, this formula determines a "start date". The "first of month" is calculated with the DAY function here:

=B5-DAY(B5)+1

Then n * 7 days to the first of the month, to get a start date n weeks from the first of the month. In the generic form of the formula, n represents "nth".

The next step is to calculate the adjustment required to reach the final result. The adjustment is calculated using WEEKDAY and DAY:

WEEKDAY(B5-DAY(B5)+8-C5)

Inside WEEKDAY, the first of the month is again calculated, then 8 days are added and the value for dow (day of week) is subtracted.

Day of week is abbreviated "dow" in the generic form of the formula. This is a number between 1 (Sunday) and 7 (Saturday) which can be changed as needed.

Finally the calculated adjustment is subtracted from original start date to get the final result.

Get nth day of week in month

Generic formula 

=date-DAY(date)+1+n*7-WEEKDAY(date-DAY(date)+8-dow)

Related formulas 

Get last working day in month

Get last weekday in month

Explanation

To get the nth day if week in a month (i.e. the first Tuesday, third Tuesday, forth Thursday, etc.) you can use a formula based on the DAY and WEEKDAY functions.

In the example shown, the formula in E5 is:

=B5-DAY(B5)+1+D5*7-WEEKDAY(B5-DAY(B5)+8-C5)

How this formula works

First, this formula determines a "start date". The "first of month" is calculated with the DAY function here:

=B5-DAY(B5)+1

Then n * 7 days to the first of the month, to get a start date n weeks from the first of the month. In the generic form of the formula, n represents "nth".

The next step is to calculate the adjustment required to reach the final result. The adjustment is calculated using WEEKDAY and DAY:

WEEKDAY(B5-DAY(B5)+8-C5)

Inside WEEKDAY, the first of the month is again calculated, then 8 days are added and the value for dow (day of week) is subtracted.

Day of week is abbreviated "dow" in the generic form of the formula. This is a number between 1 (Sunday) and 7 (Saturday) which can be changed as needed.

Finally the calculated adjustment is subtracted from original start date to get the final result.