61. Excel Tips - Last Friday of the month
Here's a tip for those of you who need to prepare end of month reports.
There's a way to calculate the last Friday of the month in Excel.
Here's how it looks in Excel:
So first, you enter a date.
- I have entered TODAY() as an Excel function.
Then the calculations do the following:
- Calculate the First Day of next month
- =DATE(YEAR(B3),MONTH(B3)+1,1)
- This says: Make a date out of: the year of our date the month of our date +1 (next month) day 1
- Weekday of First Day of next month
- =WEEKDAY(C3)
- The WEEKDAY formula returns 1 for Sunday, 2 for Monday etc..
- Last Saturday in this month
- =C3-D3
- =First Day of next month - Weekday of First Day of next month
- Last Friday in this month
- = Last Saturday in this month - 1
Odd, but it works.
Download sheet to practise calculating the last friday of the month
Training Video on how to find the last Friday of The Month:
| Attachment | Size |
|---|---|
| last-friday-of-the-month-1.xls | 19 KB |
»
- Nick's blog
- Add new comment
- 1099 reads
Results are a week off if the last day of the month is a Friday
This is more difficult than it looks. Change the input date to some time in April 2010, and the formula will tell you that the last Friday of the month is Fri 23-Apr-10. The last Friday of that month is Fri 30-Apr-10.
I've been having the wrong kind of fun with this problem as my manager has scheduled a report due on the second last Friday of every month. Setting up my calendar is a pain ...
last friday of the month
good point, I have fixed this now in the download.
Nick