Xth Weekday of the Month/Year
"ZuludogM" wrote in message
...
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.
|