How to find the first weekday of the month in Excel
At a glance
In the previous post I covered how you can return the last weekday of the month. This post will focus on the formula that returns the first weekday of the month.
Assuming cell A1 contains a date the formula is:
=EOMONTH(A1,-1)+1+CHOOSE(WEEKDAY(EOMONTH(A1,-1)+1,2),0,0,0,0,0,2,1)
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 it Works
We will break the formula into two parts and explain both parts.
As discussed in the previous post, most days of the week are weekdays; we only need to handle Saturdays and Sundays. Both these days need to return the next Monday. So we need to add 2 to a Saturday start date and 1 to a Sunday date.
The first part of the formula identifies the first calendar day of the month.
=EOMONTH(A1,-1)+1
The EOMONTH function returns the last day of the month. We have manipulated it to return the first day. By using -1 as the second argument of the EOMONTH function you are finding the last day of the previous month. Hence, we add 1 to the end of the previous month date to return the first day of the month we require.
Note: this formula is repeated later in the formula.
We only need to adjust the first day if it falls on a Saturday or a Sunday. That is what the CHOOSE function does.
+CHOOSE(WEEKDAY(EOMONTH(A1,-1)+1,2),0,0,0,0,0,2,1)
The first argument of the CHOOSE function is a whole number. What follows is a sequence of entries that you can choose from (hence the name). So the following CHOOSE function will return Apples.
=CHOOSE(2,"Oranges","Apples","Pears")
If the number was 1, then Oranges would be displayed, and 3 would display Pears.
Hence, the seven values at the end of the CHOOSE function 0,0,0,0,0,2,1 represent the values to add to the first day of the month from Monday to Sunday. The first 5 values use zero as no adjustment is necessary. The last 2 values represent Saturday and Sunday respectively.
The WEEKDAY function returns a number representing the day of the week of the first day of the month (notice the EOMONTH function is repeated). The ,2 at the end of the WEEKDAY function instructs EXCEL to return 1 for Monday, 2 for Tuesday all the way up to 7 for the Sunday. The result of the WEEKDAY function will automatically choose the correct value to add to the first day to return a weekday.
The CHOOSE function is flexible and can be used to replace multiple IF functions in certain situations.
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].
