Calculate the Date of the Last Weekday of the Month

To calculate the date of the last weekday of the month, we need to use two Excel functions: EOMONTH and WEEKDAY.

• The EOMONTH function returns the last day of the month, n months in the past or future. For example, EOMONTH(“1/15/2021”,0) returns “1/31/2021”, which is the last day of January 2021. EOMONTH(“1/15/2021”,1) returns “2/28/2021”, which is the last day of February 2021.
• The WEEKDAY function returns a number between 1 and 7 representing the day of the week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this can be changed by using a different return type argument. For example, WEEKDAY(“1/31/2021”) returns 1, which means Sunday. WEEKDAY(“1/31/2021”,2) returns 7, which means Sunday, but with a different numbering system where Monday is 1 and Sunday is 7.

By combining these two functions, we can find the date of the last weekday of the month for any given date and weekday number.

Procedures

The general formula to get the last weekday of the month is:

=EOMONTH(date,0)+1-WEEKDAY(EOMONTH(date,0)+1-dow)

where date is any valid date and dow is the weekday number between 1 and 7.

The formula works as follows:

• First, we use EOMONTH(date,0) to get the last day of the month for the given date. Then we add 1 to get the first day of the next month. For example, if the date is “1/15/2021”, we get “2/1/2021”.
• Next, we subtract the weekday number from the first day of the next month, and use WEEKDAY to get the number of days to roll back. For example, if the weekday number is 5, which means Thursday, we get WEEKDAY(“2/1/2021”-5,2), which is 4.
• Finally, we subtract the roll back days from the first day of the next month, which gives us the last weekday of the month. For example, we get “2/1/2021”-4, which is “1/28/2021”, the last Thursday of January 2021.

Explanation

To understand the formula better, let’s look at an example with real data. Suppose we have a table like this:

We want to fill the last column with the dates of the last weekdays of the month for each row. We can use the formula =EOMONTH(B2,0)+1-WEEKDAY(EOMONTH(B2,0)+1-C2) in cell D2 and copy it down to the rest of the cells. The result is:

We can see that the formula correctly returns the dates of the last weekdays of the month for each row. For example, the last Monday of January 2021 is 1/25/2021, the last Tuesday of February 2021 is 2/23/2021, and so on.

Other approaches

There are other ways to calculate the date of the last weekday of the month in Excel, such as using the WORKDAY function or the CHOOSE function. Here are some examples:

• Using the WORKDAY function: =WORKDAY(EOMONTH(date,0)+1,-1,holidays)
  • This formula works by finding the first working day of the next month, then subtracting one working day, taking into account any holidays. For example, if the date is “1/15/2021” and the holidays are “1/1/2021” and “1/18/2021”, the formula returns “1/29/2021”, which is the last working day of January 2021.
  • The advantage of this formula is that it can handle holidays, but the disadvantage is that it assumes that weekends are Saturday and Sunday, and that weekdays are Monday to Friday. If you need to customize the weekends, you can use the WORKDAY.INTL function instead.
• Using the CHOOSE function: =EOMONTH(date,0)-CHOOSE(WEEKDAY(EOMONTH(date,0)),0,1,2,3,4,5,6)
  • This formula works by finding the last day of the month, then subtracting a number based on the day of the week, using the CHOOSE function. For example, if the date is “1/15/2021”, the formula returns “1/29/2021”, which is the last Friday of January 2021.
  • The advantage of this formula is that it is shorter and simpler than the original formula, but the disadvantage is that it only works for weekdays from Monday to Friday. If you need to find the last Saturday or Sunday of the month, you need to modify the formula accordingly.