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:
Function fwim(d As Date, wd As Integer, Optional i As Integer = 1) As Date
'Fwim [First weekday in month] returns i-th weekday wd of month d:
'd - date of first day of desired month but can be any day in desired month
'wd - weekday: 1-Sunday, 2-Monday, ..., 7-Saturday
'i - 1 will return first, 2 second, etc.
'Reverse("moc.LiborPlus.www") V0.20 PB 12-Sep-2009
Dim dr As Date, wd0 As Integer
dr = DateSerial(Year(d), Month(d), 1) 'Ensure to start with first day of month
wd0 = wd Mod 7 'Saturday = 0
fwim = Int((dr + ((dr Mod 7) > wd0) * (wd0 - 6) + (i - 1) * 7) / 7) * 7 + wd0
End Function
An alternative function by Rick Rothstein is:
Function NthWeekday(DateIn As Date, Nth As Long, _
DOW As Long) As Date
NthWeekday = DateSerial(Year(DateIn), _
Month(DateIn), 1 + 7 * Nth) - _
Weekday(DateSerial(Year(DateIn), _
Month(DateIn), 8 - DOW))
End Function
Function lwim(d As Date, wd As Integer, Optional i As Integer = 1) As Date
'Lwim [Last weekday in month] returns last but (i-1)-th weekday wd of month d:
'd - date of last day of desired month but can be any day in desired month
'wd - weekday: 1-Sunday, 2-Monday, ..., 7-Saturday
'i - 1 will return last, 2 last but one, etc.
'Reverse("moc.LiborPlus.www") V0.20 PB 12-Sep-2009
Dim dr As Date, wd0 As Integer
dr = DateSerial(Year(d), Month(d) + 1, 0) 'Ensure to start with last day of month
wd0 = wd Mod 7 'Saturday = 0
lwim = Int((dr + ((dr Mod 7) < wd0) * wd0 - (i - 1) * 7 + 6) / 7) * 7 + wd0 - 7
End Function
Fwim_Lwim.xlsm
25KB
Binary
Fwim_Lwim.xlsm
Last modified 2yr ago