If you use Excel to maintain schedules and/or time sheets, you might have needed to find the date of a specific day of the month. For example, what date does the second Monday of the month fall on? If you ever need to look up this same information but don’t want to use a calendar to do it, there’s a simple formula that you can use instead.
Variables
We’re dealing with the days in a week, and the months in a year so while there are variables in this formula, their scope is limited. That said, we’re going to give you the formula and explain which variable is which.
Year: The year of the month you want to look up the date for.
Month: The month you want to look up the date for.
Day of week: The day of the week you want to look up the date for.
Occurrence: The occurrence of the day i.e., first Monday, or second Friday, or third Wednesday.
These variables aren’t going to be used by name. All you can do is use cell addresses however, the formula will be much easier to customize for your own needs when it’s properly formatted. The above variables will act as column headers so that it’s easier for you to understand what is going on and edit the formula.
Formula
The generic formula is as follows however you will need to edit it with the example given so that it can return a date. Paste the following in an empty cell and use the example to edit it accordingly.
=DATE(A8,B8,1+7*D8)-WEEKDAY(DATE(A8,B8,8-C8))
Example
Months are numbered 1-12 starting with January. Days of the week are numbered 1-7 starting with Sunday which means Sunday is equal to 1, Monday is equal to 2, Tuesday is equal to 3, and so on.
In the above formula, the values that need to be edited to your needs are;
A8, B8, D8, and C8.
A8 = Year, B8 = Month, C8 = Day of week, and D8 = Occurrence
The editing is the only tricky part but once you’ve figured that out, it’s going to be easy to use this formula. It comes from Superuser user ale.
If you’re nervous about whether or not you’ve edited the values correctly, try a few out and use a calendar to check if you’re getting the correct results. The only confusing bit is between the day of the week and its occurrence. Remember that the value for the day of the week can be as high as 7 whereas its occurrence cannot go above 5.
