Excel tips: how to find the last weekday of the month
At a glance
If you have a date in cell A1 and you want to know the last weekday (Monday to Friday) of the month for that date you can use the following formula
=EOMONTH(A1,0)-MAX(0,WEEKDAY(EOMONTH(A1,0),2)-5)
This formula will work with Excel 2007 and later versions. If you are using Excel 2003 you must install the Analysis Toolpak Add-in to use the EOMONTH function.
How does it work?
The start of the formula establishes the last day of the month
EOMONTH(A1,0)
Obviously if this is a weekday we can use the date itself with no adjustment. 5 out of the 7 days are weekdays. We need to amend only those dates that fall on a Saturday or Sunday.
The last part of the formula does that.
-MAX(0,WEEKDAY(EOMONTH(A1,0),2)-5)
The MAX function finds the highest value. The MAX function starts with a zero which means it will ignore negatives and display zero if a negative is included as the other value. The WEEKDAY function returns a number representing the day of the week. It will return a number for the last day of the month. The ,2 near the end of the formula means the WEEKDAY function will return 1 for Monday, 2 for Tuesday; all the way to 7 for Sunday.
Friday is 5, this is why we subtract 5 from the WEEKDAY result. After deducting the 5, Saturday (6) and Sunday (7) will return 1 and 2 respectively. The other days of the week will return either zero (Friday) or a value less than 0. The MAX function will return 0 for any negatives since it has zero at the start.
If the month ends on a Saturday the MAX function will return 1 and that value is deducted from the last day of the month to return a Friday. A Sunday will return 2 and once deducted will also return the Friday date.
The above formula doesn’t take into account public holidays.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected].
