Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xth Weekday of the Month/Year
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xth Weekday of the Month/Year
On Wed, 15 Jun 2005 03:56:02 -0700, "ZuludogM"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
Which historical year will offer the same weekday ... ? | Excel Discussion (Misc queries) | |||
find date of last weekday of a month | Excel Discussion (Misc queries) | |||
Weekday formula calculating to end of month | Excel Worksheet Functions | |||
WEEKDAY question on year | Excel Discussion (Misc queries) |