How to find first or last Friday of every month in Excel?
Normally Friday is the last work day in a month. How can you find the first or last Friday based on a given date in Excel? In this article, we will guide you through how to use two formulas for finding the first or last Friday of every month.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
For example, there is a given date 1/1/2015 locates in cell A2 as below screenshot shown. If you want to find the first Friday of the month based on the given date, please do as follows.
1. Select a cell to display the result. Here we select the cell C2.
2. Copy and paste the formula =CEILING(EOMONTH(A2,-1)-5,7)+6 into the Formula Bar, then press the Enter key. See screenshot:
Then the date is displayed in cell C2, it means that the first Friday of January 2015 is the date 1/2/2015.
1. If you want to find the first Friday of other months, please enter the specified date of that month into cell A2, and then use the formula.
2. In the formula, A2 is the reference cell which the given date locates. You can change it to meet your needs.
The given date 1/1/2015 locates in cell A2, for finding the last Friday of this month in Excel, please do as follows.
1. Select a cell, copy and paste the formula =DATE(YEAR(A2),MONTH(A2)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,0),2)-2,-7) into the Formula Bar, and press the Enter key to get the result. See screenshot:
Then the last Friday of January 2015 is displaying the cell B2.
Note: You can change A2 in the formula to the reference cell of your given date.
- How to find the lowest and highest 5 values in a list in Excel?
- How to find or check if a specific workbook is opened or not in Excel?
- How to find out if a cell is referenced in other cell in Excel?
- How to find closest date to today on a list in Excel?
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 5 months agoHello and thank you for this formula. I am not an Excel guru and acnnot easily work out how to change this formula for the last Friday to be the Last Monday, or any other day. Can you please explain how to interpret this.
To post as a guest, your comment is unpublished.· 5 months agoMatt,
The way the formula works is: First we find the last day of the month with the EOMONTH() function. For the last day of the current month, we use "0" as the month offset. For the previous month, it would be -1. (Either "=EOMONTH(A2,0)" or "=EOMONTH(A2,-1)".)
Then, in the "=CEILING()" function, we subtract the number of days required to bring us back to Sunday from the day for which we are searching. (For Tuesday, it would be -2, so we make the formula so far: "=CEILING(EOMONTH(A2,0)-2...."
We use the "significance" value of "7" because weeks are seven days long (of course).This finds the date of either the previous Saturday, or the following Saturday (Excel dates started with 1/1/1900, which was a Sunday. So if you enter a "1" in cell A2 with the Long Date format, the cell would read: "Sunday, January 1, 1900".). For that reason, the =CEILING() function, using "7" as the "significance" argument returns Saturday, which is always a multiple of 7 in Excel. Now the formula is "=CEILING(EOMONTH(A2,0)-2,7...".
Now we need to return the date to a Tuesday, so we either subtract 4 for the last Tuesday of the month, or add 3 for the first Tuesday of the next month. So the final formula is, respectively, either "=CEILING(EOMONTH(A2,0)-2,7)-4" or "=CEILING(EOMONTH(A2,0)-2,7)+3".
We can work through the math by assuming the last day of the month is either a Monday (assume its value is 9 - for ease of illustration, so the values do not go negative) or a Wednesday (assume value 11). "EOMONTH()" will return either of those days (9 or 11). Subtracting 2 results in the previous Saturday (value 7) or Monday (value 9), respectively. The CEILING() function then returns, respectively, the previous Saturday (value 7, because the ceiling of 7 with significance 7 is 7) or the current Saturday (value 14, because the ceiling of 9, significance 7, is 14). Subtracting 4 results in the previous Tuesday (value 3) or the current week's Tuesday (value 10), respectively. For the first Tuesday of the following month, adding 3 to the Saturday value (7 or 14) returns either Tuesday value 10, or Tuesday value 17, respectively.
Hopefully that helps you understand so that you can modify the formula as necessary. You can even change it to find the second Tuesday, or second to last Tuesday, by adding or subtracting an additional 7, respectively.
To post as a guest, your comment is unpublished.· 5 months agoHi Matt,
You just need to change the last number -7 to -1 to solove the problem.