Hello Excellers welcome to more #formulafriday fun, today let’s look at a query that is a actually a widely used calculation for a lot of different scenarios- calculating a date of a certain weekday in every month. For example-

• Calculate the date if you get paid on the second Tuesday of every month
• I calculate the date of the issue of my Newsletter which is issued the First Tuesday of every month
• A meeting which occurs on the last Friday of every month
• The date of Thanksgiving in the USA

As you can see there a a lot of reasons why you would need to use this calculation. So let’s dive into some formula fun and use my reason for this formula as an example, calculating the date my Excel At Excel Newsletter is due to be issued, on the FIRST WEDNESDAY of every month.

We will use a few functions to achieve the result, both DAY and WEEKDAY.

First of all the DAY formula determines the start date of the month or first of the month. The syntax of the DAY function is as follows –

=DAY(serial_number)

Where

• Serial_number This is required. It returns the Day of the month from 1 to 31 you are trying to find.

Second we use the WEEKDAY function. The syntax of the this function is as follows

Where

• Serial_number This argument is required. It is sequential number that represents the date of the day you are trying to find.
• Return_type. This argument is optional. It a a number which determines the type of return value. A summary of the return type can be seen below.

So, lets get building our formula to determine the date that my Excel At Excel Newsletters are due for the full year 2017. (The first Tuesday of every month). First I have generated a list of Months in Column B on my Excel worksheet.

• We first use the DAY function to determine the start of our months, or the first day of the month. This part of the formula looks like this-

=B2-DAY(B2)+1

• We then add 1*7 to the first of the month which represents 1 week from the first of the month. If we were calculating the second week of the month then we would use 2*7 and so on.

=B2-DAY(B2)+1+1*7

• The next step is to calculate the adjustment required to reach the final result. The adjustment is calculated using WEEKDAY and DAY as the 8 days are added, and the value for the day of the week is subtracted. In this case it is 3 representing Tuesday.

WEEKDAY(B2-DAY(B2)+8-3)

The result of the WEEKDAY part of the formula is 5 which is subtracted from the first part of the formula (08/01/2017) resulting in 03/01/2017. We can then drag the formula the all 12 months of the year and I now know the dates that my How to Excel At Excel Newsletter is due.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.