Sulprobil
Search…
Weekday in Month
What if you like to determine the Sunday of the third “full” weekend in a month? A full weekend means Saturday and Sunday. In March 2009 this would be Sunday 22nd of March 2009, for example.
Solution: Add one day to the third Saturday of that month.
=fwim(DATE(2009,3,1),7,3)+1
[see user defined function fwim() below, please]
The general solution for the i-th weekday of a month:
1
Function fwim(d As Date, wd As Integer, Optional i As Integer = 1) As Date
2
'Fwim [First weekday in month] returns i-th weekday wd of month d:
3
'd - date of first day of desired month but can be any day in desired month
4
'wd - weekday: 1-Sunday, 2-Monday, ..., 7-Saturday
5
'i - 1 will return first, 2 second, etc.
6
'Reverse("moc.LiborPlus.www") V0.20 PB 12-Sep-2009
7
Dim dr As Date, wd0 As Integer
8
dr = DateSerial(Year(d), Month(d), 1) 'Ensure to start with first day of month
9
wd0 = wd Mod 7 'Saturday = 0
10
fwim = Int((dr + ((dr Mod 7) > wd0) * (wd0 - 6) + (i - 1) * 7) / 7) * 7 + wd0
11
End Function
Copied!
An alternative function by Rick Rothstein is:
1
Function NthWeekday(DateIn As Date, Nth As Long, _
2
DOW As Long) As Date
3
NthWeekday = DateSerial(Year(DateIn), _
4
Month(DateIn), 1 + 7 * Nth) - _
5
Weekday(DateSerial(Year(DateIn), _
6
Month(DateIn), 8 - DOW))
7
End Function
Copied!
1
Function lwim(d As Date, wd As Integer, Optional i As Integer = 1) As Date
2
'Lwim [Last weekday in month] returns last but (i-1)-th weekday wd of month d:
3
'd - date of last day of desired month but can be any day in desired month
4
'wd - weekday: 1-Sunday, 2-Monday, ..., 7-Saturday
5
'i - 1 will return last, 2 last but one, etc.
6
'Reverse("moc.LiborPlus.www") V0.20 PB 12-Sep-2009
7
Dim dr As Date, wd0 As Integer
8
dr = DateSerial(Year(d), Month(d) + 1, 0) 'Ensure to start with last day of month
9
wd0 = wd Mod 7 'Saturday = 0
10
lwim = Int((dr + ((dr Mod 7) < wd0) * wd0 - (i - 1) * 7 + 6) / 7) * 7 + wd0 - 7
11
End Function
Copied!
Fwim_Lwim.xlsm
25KB
Binary
Fwim_Lwim.xlsm
Last modified 1yr ago
Copy link