Formula to find day of week occurrences in Excel

by
advanceddates

In my previous post I showed a function that can help pull a certain instance of a day of the week using VBA. In this post, I’ll show how to do that without VBA. Specifically, three things this post will look at: 1) how to find the first Monday of the month 2) how to find the date ending/starting of a week, and 3) how to find the 3rd Monday of the month

1.Finding the first Monday of the month

First, I need to identify the weekday that the first day of the year falls on with this formula:

Formula 1.A

=WEEKDAY(DATE(2014,1,1))

This returns 4 (Wednesday). 

Next I’ll determine how many days away this is from my desired day – Monday. Since Monday is the 2nd day of the week, the first day of the year is 2 (4-2) days after Monday. If you want to use another day instead of Monday then change the 2 to the corresponding day of the week (for example, Sunday is 1 – assuming your settings are setup for weeks starting on a Sunday). 

With 7 days in a week, I need to deduct 2 from 7, making 5 days that I need to add to the first day of the year to get to the first Monday of the year. The formula so far looks like this:

Formula 1.B

=DATE(2014,1,1)+7-(WEEKDAY(DATE(2014,1,1))-2)

However, this formula will only work if the first day of the month falls after Monday. I need to include an IF statement so that in the case that Monday (or whatever day I choose) falls after the first day of the year, it will just add the difference. The formula that follows includes this consideration:


Formula 1.C

=DATE(2014,1,1)+IF(WEEKDAY(DATE(2014,1,1))-2<=0,2-WEEKDAY(DATE(2014,1,1)),7-((WEEKDAY(DATE(2014,1,1))-2)))

(the numbers highlighted in red relate to the day of the week I want to calculate)

This formula is a bit more complex but all it is saying is that if the difference between the days of the week are negative (e.g. first day of the year is Sunday and hence before Monday) then I would just add the difference to the first day of the month. Otherwise the formula remains as it was.

I’ve replaced all the possible variables with words so you can easily see where to change the values to calculate to the day you want.


Formula 1.D

=DATE(YEAR,MONTH,1)+IF(WEEKDAY(DATE(YEAR,MONTH,1))-WEEKDAYNUMBER<=0,WEEKDAYNUMBER-WEEKDAY(DATE(YEAR,MONTH,1)),7-((WEEKDAY(DATE(YEAR,MONTH,1))-WEEKDAYNUMBER)))

2. How to find the 3rd Monday of the month

Continuing from Formula 1.D, I’m going to make an adjustment to calculate the proper week. To do this I need to add two weeks to the formula:

Formula 2.A

=DATE(YEAR,MONTH,1)+((XTHOCCURRENCE-1)*7)+IF(WEEKDAY(DATE(YEAR,MONTH,1))-WEEKDAYNUMBER<=0,WEEKDAYNUMBER-WEEKDAY(DATE(YEAR,MONTH,1)),7-((WEEKDAY(DATE(YEAR,MONTH,1))-WEEKDAYNUMBER)))


Where XTHOCCURRENCE in this case will be equal to 3.

If your result is a number you will need to change the cell format (CTRL+1) to date.


3. Find the date ending/starting of a week

If you have weekly reporting and reference a week number, you may find it useful to show what date that week relates to.

This formula builds off of Formula 1.D. One difference is the month will always be set to 1 because we need to know the first weekday of the year. This is important to determine the number of weeks that need to be added.


If the first day of the year falls after the day I want (this example is Monday), then I know the first Monday will fall in week 2, not in week 1. What that means is if I want to determine the Monday on week 15, I will need to multiply by 13 and not by 14 weeks to get to that date. The formula is:

Formula 3.A

=DATE(2014,1,1)+IF(WEEKDAY(DATE(2014,1,1))-2<=0,(15-1)*7+2-WEEKDAY(DATE(2014,1,1)),(15-2)*7+7-((WEEKDAY(DATE(2014,1,1))-2)))

The only parts I added were those in purple, which just multiply the number of weeks I need. This formula will work for any day of the week. So if you want to calculate the the start or end of week 15, you can set the weekday you are calculating to as Monday (2) or for the end of the week to Friday (6). Here is the formula with the variables replaced with words:

=DATE(YEAR,1,1)+IF(WEEKDAY(DATE(YEAR,1,1))-WEEKDAYNUMBER<=0,(WEEKNUMBER-1)*7+WEEKDAYNUMBER-WEEKDAY(DATE(YEAR,1,1)),(WEEKNUMBER-2)*7+7-((WEEKDAY(DATE(YEAR,1,1))-WEEKDAYNUMBER)))

The weekday number relates to the day of the week (e.g. 1-Sunday, 2-Monday, 3-Tuesday, 4-Wednesday, 5-Thursday, 6-Friday, 7-Sunday). This may be slightly different depending on your regional settings, you may have Monday set as 1. The weekday number is the day of the week that you’re interested in determining where it falls.

Add a Comment

You must be logged in to post a comment