< Go Back

Get last weekday in month

How to Get last weekday in a month?

In order to get the last weekday in month i.e. last Thursday, last Friday etc, we could use EOMONTH and WEEKDAY functions.

What is EOMONTH()?

EOMONTH() is a function that returns a serial date after calculating the last day of the month.

What is WEEKDAY()?

WEEKDAY() returns a number i.e. 1 to 7 representing a weekday. Sunday is represented as 1 and Saturday is represented as 7.

Formula

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

Let us go through an example to understand this formula.

How to use the Formula to Get Last Weekday in Month?

Figure 1. Example 1 of formula to get last week in a month

In this example, we created a small table containing dates. We want to know on what date a particular weekday will occur for the last time in a month. The weekday column represents a weekday’s number. 1 being Sunday and so on.

  1. Select any column, where you want to display the last date of a particular weekday. In this case, we selected F5.
  2. Go in the function box and type
    =EOMONTH(C5,0)+1-WEEKDAY(EOMONTH(C5,0)+1-D5)
  3. EOMONTH(C5,0) will give the last date of a month. Adding one would give the start of next month. In this case, it would be June 1st.
  4. =WEEKDAY(EOMONTH(C5,0)+1-D5) calculates the number of days required to roll back to requested weekday in previous month.
  5. =EOMONTH(C5,0)+1 returns first of next month. Requested weekday D5 i.e. 7 in our example is subtracted, which is then fed to WEEKDAY().
  6. The result yielded by WEEKDAY() is then subtracted from the first part of the formula i.e. EOMONTH(C5,0)+1.
  7. Using this formula you can get last weekday in month.
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