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
- Login or register to post comments
- 50463 reads
Computing the expiry date (last Thursday of every month)...
Hi:
In India, the stock futures expire on the last Thursday of every month.
At any given point, there are 3 active contracts for every stock & index future, Current month (the present month), Near month (first month following the current month) and Far month (second month following the current month).
The expiry for every contract is the last Thursday of the respective month. If the last Thursday happens to be a holiday, expiry will be the Wednesday before the last Thursday. If this Wednesday also happens to be a holiday, expiry will be on the Tuesday preceding this Wednesday.
List of Holidays
No. Date Day
1 26-Jan-11 Wednesday
2 02-Mar-11 Wednesday
3 12-Apr-11 Tuesday
4 14-Apr-11 Thursday
5 22-Apr-11 Friday
6 15-Aug-11 Monday
7 31-Aug-11 Wednesday
8 01-Sep-11 Thursday
9 06-Oct-11 Thursday
10 26-Oct-11 Wednesday
11 27-Oct-11 Thursday
12 07-Nov-11 Monday
13 10-Nov-11 Thursday
14 06-Dec-11 Tuesday
15 26-Jan-12 Thursday
16 20-Feb-12 Monday
17 08-Mar-12 Thursday
18 05-Apr-12 Thursday
19 06-Apr-12 Friday
20 01-May-12 Tuesday
21 15-Aug-12 Wednesday
22 20-Aug-12 Monday
23 19-Sep-12 Wednesday
24 02-Oct-12 Tuesday
25 24-Oct-12 Wednesday
26 13-Nov-12 Tuesday
27 14-Nov-12 Wednesday
28 28-Nov-12 Wednesday
29 25-Dec-12 Tuesday
I want to compute the number of days to expiry everyday... for example, the expiry dates for the active contracts are:
Current month: April 26, 2012 (7 days from today)
Near month: May 31, 2012 (42 days from today)
Far month: June 28, 2012 (70 days from today)
Hence, I want to know how many days left for expiry on a continuous basis.
Thanks in advance....
Cheers,
Navin Agrawal
nbagrawal.2@gmail.com
this is a bit involved..best
this is a bit involved..
best to request a quote:
http://excelexperts.com/contact
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
Correct via VBA:
Add to a VBA module. Use within sheet as: =LastSunday(X)
Where X denotes month required of this year - code is easily augment to take any year!
Public Function LastSunday(Months_Ahead As Long) As Date
'# Hardeep
Dim sdate As Date ' First day of next month
sdate = VBA.DateSerial(VBA.Year(VBA.Date), Months_Ahead + 1, 1)
If VBA.Month((sdate - VBA.Weekday(sdate, 1)) + 1) <> VBA.Month(sdate) Then
LastSunday = (sdate + 1 - VBA.Weekday(sdate + 1, 1)) + 1
Else
LastSunday = (sdate - VBA.Weekday(sdate, 1)) + 1
End If
End Function
#Correction - with Year also#
Public Function LastSunday(Month_ As Long, Year_ As Long) As Date
'# Hardeep
Dim sdate As Date ' First day of next month
sdate = VBA.DateSerial(Year_, Month_ + 1, 1)
If VBA.Weekday(sdate, 1) = 1 Then
LastSunday = sdate - 7
Else
LastSunday = (sdate - VBA.Weekday(sdate, 1)) + 1
End If
End Function