How to Get the nth Day of the Week in a Month in Excel

This Excel function can be used to know the day of a particular weekday in a month.

Get nth day of a week in a month in Excel

Formula using WEEKDAY and DAY functions

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

Explanation of formula

When using Excel to get the nth day of the week in a particular month, the formula can be used based on two different functions – the WEEKDAY and DAY functions. For instance, you want to know the first Monday, the second Tuesday, third Wednesday, or the fourth Thursday in a month.

The formula to get the nth day of the week in a month works by first picking the start date. On the other hand, the “n * 7” element, works to get the number of days from the first of the month.

dow is an abbreviation for the day of the week.

“dow” is any number in the range of 1- 7 (i.e., from Sunday to Saturday). You can always alter the numbers as you want.

Example

In this example, we are required to get the different days of the week for a certain month – January.

Step 1: We have a list of dates, which could all be the same or different. We would explore both in this example.

Figure 1. Enter same (or different) dates in a month.

Step 2: Note that for this next step, we’re using different dates for clarity.

Enter the formula as shown in the image. As you can see, the formula returns the result as “43107”.

Figure 2. Enter the formula to get the nth day of the week in a month.

Step 3: Click on cell “D2” and choose “format cell,” where you can change the format of the date.

Figure 3. Change the date format in your Excel spreadsheet.

Step 4: Changing the date format returns something like this.

Figure 4. Excel shows the date format of the nth day of the week.

Step 5: A simple drag and drop of the “Returned Results” column returns all the nth days of the week in the month.

Figure 5. Drag and drop show each nth day of the week in a month.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar