- To post as a guest, your comment is unpublished.· 1 years agoHi Eric,
Thanks for sharing it.
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.
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 below formula into it, then press the Enter key.
Then the date is displayed in cell C2, it means that the first Friday of January 2015 is the date 1/2/2015.
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 the below formula into it, and then press the Enter key to get the result.
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?
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.· 1 years agoWhy not make the "last Friday" formula: =CEILING(EOMONTH(A2,0)-5,7)-1? That works for all dates I have tried....
- To post as a guest, your comment is unpublished.· 1 years 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.· 1 years 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.· 1 years agoHi Matt,
You just need to change the last number -7 to -1 to solove the problem.