I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

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:

20090912_weekday_first

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
'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

The general solution for the last or the last but (i-1)-th weekday of a month:

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

20090912_weekday_last

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
'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

Sulprobil   Get it done   Contact   Disclaimer   Download