Xth Weekday of the Month/Year

[Guest]

I ran in to a need for a function that would give the date of the 3rd Sunday
in October and the First Thursday in April. This is a common problem when
trying to adjust time zone differences internationally. e.g. what time is it
in Cairo and Sydney when it is 10:00AM in London on 3 March 2005. What about
4 June 2006? etc.

You can do this through a macro or non-macro solution. The non-Macro would
have a formula that looks like this for getting the Last Weekday in the Month:
=EOMONTH(C14,0) - Mod(Mod(7-Mod(L14,7),7)+WeekD(EOMONTH(C14,0)),7)

The non-Macro would look like this for getting the First Weekday in the Month:
=DATE(YEAR(C14),MONTH(C14),1) +
Mod(7-Mod(Mod(7-Mod(L14,7),7)+WeekD(DATE(YEAR(C14),MONTH(C14),1)),7),7) +
(J14-1)*7

Where C14 is the date that contains the target Month and Year. L14 contains
the weekday (using microsoft weekdays: 1 is Sat, 2 is Sun, ... 7 is Fri).
J14 is the number of occurrences; 1 would be the 1st weekday (e.g. Tuesday)
of the month, 2 would be the 2nd etc.

If you wanted to know the Xth weekday of the year (e.g. what is the 24th
Wednesday of 2006), then set the month to January. (e.g. C14 would equal
1/1/2006, L14 would equal 5 for Wed, and J14 would equal 24).

Here is the macro version. You can implement this as a function:
XthWeekDay(Occurrence, WeekD, Mo, Yr)
e.g. the Last Tuesday in November 2005 would be:
=XthWeekDay("Last", 4, 11, 2005)
the 1st Tuesday in November 2008 would be:
=XthWeekDay(1, 4, 11, 2005)
the 32nd Saturday in 2011 would be:
=XthWeekDay(32, 1, 1, 2011)

Public Function XthWeekDay(Occurrence As Variant, WeekD As Integer, Mo As
Integer, Yr As Integer)
'Return the Xth Weekday of a given month and year. Use "Last" for last and
a number for 1st, 2nd, etc.
'Mark Fitzpatrick

Dim OffSetDay As Date

'Set Offset Day
be = 0
If Occurrence = "Last" Then be = 1

If Mo = 12 Then
OffSetDay = DateSerial(Yr, 12, 30 * be + 1)
Else
OffSetDay = DateSerial(Yr, Mo + be, 1) - be
End If


'Set the Xth Day
If Occurrence = "Last" Then
XthWeekDay = OffSetDay - Modulo(Modulo(7 - Modulo(WeekD, 7), 7) +
Weekday(OffSetDay), 7)
Else
XthWeekDay = OffSetDay + Modulo(7 - Modulo(Modulo(7 - Modulo(WeekD, 7),
7) + Weekday(OffSetDay), 7), 7) + 7 * (Occurrence - 1)
End If

End Function


Private Function Modulo(x As Integer, y As Integer) As Integer

Modulo = Abs(x / y - Int(x / y)) * y

End Function

 

[Bob Phillips]

You can do this through a macro or non-macro solution. The non-Macro would
have a formula that looks like this for getting the Last Weekday in the Month:
=EOMONTH(C14,0) - Mod(Mod(7-Mod(L14,7),7)+WeekD(EOMONTH(C14,0)),7)

The non-Macro would look like this for getting the First Weekday in the Month:
=DATE(YEAR(C14),MONTH(C14),1) +
Mod(7-Mod(Mod(7-Mod(L14,7),7)+WeekD(DATE(YEAR(C14),MONTH(C14),1)),7),7) +
(J14-1)*7

You can use a generic formula for most of this

=DATE(YEAR(A1),MONTH(A1),1+instance*7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW
))

which returns the instance (instance) of a day of the week (DoW) for any
month as represented by a date in A1.

Using this, the first working day in a month wuld use instance of 1 and DoW
of 2, assuming that Monday is the first workday

The last working day is a bit different, like so

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)
-5))

If you wanted to know the Xth weekday of the year (e.g. what is the 24th
Wednesday of 2006), then set the month to January. (e.g. C14 would equal
1/1/2006, L14 would equal 5 for Wed, and J14 would equal 24).

The generic formula works for this also, with an instance of 24 and DoW of
4, and seating A1 to 01/.01/2006.

No macros needed.

 

[Ron Rosenfeld]

I ran in to a need for a function that would give the date of the 3rd Sunday
in October and the First Thursday in April. This is a common problem when
trying to adjust time zone differences internationally. e.g. what time is it
in Cairo and Sydney when it is 10:00AM in London on 3 March 2005. What about
4 June 2006? etc.

Here is another algorithm for that problem, courtesy of Daniel M.

The Nth specified weekday of a month can be given by the formula:

=A1+7*N-WEEKDAY(A1+7-DOW)

A1 contains the first date in the month of interest
DOW is the day of the week where Sun=1, Mon=2 ... Sat = 7
N is the number of the week (eg. 1st, 2nd, 3rd).

So for the 3rd Sunday in October

A1: 1 Oct 2005
DOW: 1
N: 3

Sunday, October 16, 2005

For the 24th Wednesday in the year:

A1: 1 Jan 2005
DOW: 4
N: 24

Wednesday, June 15, 2005

For the last Weekday of a month:

=A1+33-DAY(A1+32)-WEEKDAY(A1+40-DAY(A1+32)-DOW)

Where A1 contains the first date in the month of interest.

==============

If you want to have a random date in A1, rather than constraining it to be the
first of the month, then the formulas are a bit more complex:


For the Nth specified weekday of the month:

=A1+1-DAY(A1)+7*N-WEEKDAY(A1-DAY(A1)+8-DOW)

For the last specified weekday of the month:


=A1-DAY(A1)+34-DAY(A1-DAY(A1)+33)-WEEKDAY(A1-DAY(A1)+41-DAY(A1-DAY(A1)+33)-DOW)


--ron