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