Logic Problem: Convert Daily Data to Unique Weekly
Author 
Message 
Webbi #1 / 7

Logic Problem: Convert Daily Data to Unique Weekly
I've got a logic problem that is causing my gray's to get grayer. Coverting DAILY price data of stocks (Date, Open, High, Low, Close) into WEEKLY price data is not difficult. Each week ends on Friday, so I check for the first day following Friday to start my week, where I will assign the OPEN price of that first day to the week's OPEN, then check for the Highest High and Lowest Low price. When I come to Friday again, I know that the prior week has ended and I then save the HH and LL price, as well as the last price CLOSE and start my new week. Where I am confusing myself is when I want to create WEEKLY data that has an EndofWeek day other than Friday. For example, say I have a SUB routine called ConvertToWeekly. Public Sub ConvertToWeekly(ByVal DayOfWeek as Long) The DayOfWeek will be a value from 1 to 5, where 1 = Mon, 2 = Tue, 3 = Wed, with 5 being the highest for Friday. Enum DAYS Mon = 1 Tue Wed Thu Fri End Enum ConvertToWeekly(DAYS.Wed) The routine is to take DAILY data that is in an array called DataArray() and convert it to WEEKLY data, where each week ends on Wednesday (because of DAYS.Wed). The daily data contains no weekends or holidays. DataArray() elements are: .sDate as String .fOpen as Single .fHigh as Single .fLow as Single .fClose as Single So what I started out doing is looping through DataArray(zerobased) until I came to the first day of my first week. This was determined by doing the following: Public Sub ConvertToWeekly(ByVal DayOfWeek as Long) 'Default to Friday is DayOfWeek out of range If DayOfWeek < 1 or DayOfWeek > 5 then DayOfWeek = 5 'This gets the day number of the first day of week lFirstDay = DayOfWeek + 1 If lFirstDay > 5 Then lFirstDay = 1 *** At this point, I know that each week starts with lFirstDay and ends with DayOfWeek. Now I need to loop through DataArray and separate the data into my unique week data sets. These are stored into another array called WeeklyData(). Same structure as DataArray(). If my DayOfWeek = 3 for Wednesday, for example, that means my weeks will be made up of the following: 45123 Therefore, I cannot simply use tests like "If WeekDay is >= 4 and <= 3 then..." So how might I test for the start of each new week and the end of a previous week? Should I somehow convert my DayOfWeek value within the routine to reflect the first day of the week to be 1 using the WeekDay() function? lDayNum = WeekDay(DataArray(n).sDate, vbTuesday) Okay, my head hurts now and I'm going to take a break and watch Top Chef. Then keep trying to pan it out. If someone has a suggestion when I get back, great. Thanks in Advance! Webbiz (PS: I'm sure there is a simple way to do this. Whenever I ask these kinds of questions, someone pops up and says "hey, you know there is a function for that, don't you? Thing is, I haven't found it yet. :)

Wed, 14 Mar 2012 03:32:08 GMT 


Webbi #2 / 7

Logic Problem: Convert Daily Data to Unique Weekly
How stupid of me! It just dawned on me that if I'm going to determine whether the data has just ended for the current week and is now entering the next week, all I need to do is add 7 to my start date (making sure that my start date is correct of course to begin with) and then keep doing this throughout the loop. It's amazing what watching 3 episodes of Top Chef in a row can do for the mind. Think I got this covered. :) Webbiz
Quote: >I've got a logic problem that is causing my gray's to get grayer. >Coverting DAILY price data of stocks (Date, Open, High, Low, Close) >into WEEKLY price data is not difficult. Each week ends on Friday, so >I check for the first day following Friday to start my week, where I >will assign the OPEN price of that first day to the week's OPEN, then >check for the Highest High and Lowest Low price. When I come to Friday >again, I know that the prior week has ended and I then save the HH and >LL price, as well as the last price CLOSE and start my new week. >Where I am confusing myself is when I want to create WEEKLY data that >has an EndofWeek day other than Friday. >For example, say I have a SUB routine called ConvertToWeekly. >Public Sub ConvertToWeekly(ByVal DayOfWeek as Long) >The DayOfWeek will be a value from 1 to 5, where 1 = Mon, 2 = Tue, 3 = >Wed, with 5 being the highest for Friday. >Enum DAYS > Mon = 1 > Tue > Wed > Thu > Fri >End Enum > ConvertToWeekly(DAYS.Wed) >The routine is to take DAILY data that is in an array called >DataArray() and convert it to WEEKLY data, where each week ends on >Wednesday (because of DAYS.Wed). >The daily data contains no weekends or holidays. >DataArray() elements are: >.sDate as String >.fOpen as Single >.fHigh as Single >.fLow as Single >.fClose as Single >So what I started out doing is looping through DataArray(zerobased) >until I came to the first day of my first week. This was determined by >doing the following: >Public Sub ConvertToWeekly(ByVal DayOfWeek as Long) > 'Default to Friday is DayOfWeek out of range > If DayOfWeek < 1 or DayOfWeek > 5 then DayOfWeek = 5 > 'This gets the day number of the first day of week > lFirstDay = DayOfWeek + 1 > If lFirstDay > 5 Then lFirstDay = 1 > *** At this point, I know that each week starts with lFirstDay and >ends with DayOfWeek. Now I need to loop through DataArray and separate >the data into my unique week data sets. These are stored into another >array called WeeklyData(). Same structure as DataArray(). >If my DayOfWeek = 3 for Wednesday, for example, that means my weeks >will be made up of the following: > 45123 >Therefore, I cannot simply use tests like "If WeekDay is >= 4 and <= 3 >then..." >So how might I test for the start of each new week and the end of a >previous week? >Should I somehow convert my DayOfWeek value within the routine to >reflect the first day of the week to be 1 using the WeekDay() >function? >lDayNum = WeekDay(DataArray(n).sDate, vbTuesday) >Okay, my head hurts now and I'm going to take a break and watch Top >Chef. Then keep trying to pan it out. If someone has a suggestion when >I get back, great. >Thanks in Advance! >Webbiz >(PS: I'm sure there is a simple way to do this. Whenever I ask these >kinds of questions, someone pops up and says "hey, you know there is a >function for that, don't you? Thing is, I haven't found it yet. :)

Wed, 14 Mar 2012 09:27:28 GMT 


Rick Rothstei #3 / 7

Logic Problem: Convert Daily Data to Unique Weekly
I think this may help you (if not to calculate all your days, at least to get you your start date). You can calculate nth such and such day for a given month and year (that is, for example, the 2nd Thursday in October, 2009) using this oneliner statement... NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth)  Weekday( _ DateSerial(Y, M, 8  DOW), vbMonday) where Y and M are the year and month, DOW is the day of the week (using your Monday=1, Tuesday=2, etc. schedule) and Nth is which day of the week in the month you are interested in (Nth = 1 for first such and such day, Nth=2 for second such and such day, etc.); for example, if you wanted the 2nd Thursday in October 2009... Y = 2009 M = 10 Nth = 2 DOW = 4 NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth)  Weekday( _ DateSerial(Y, M, 8  DOW), vbMonday)  Rick (MVP  Excel)
Quote: > How stupid of me! > It just dawned on me that if I'm going to determine whether the data > has just ended for the current week and is now entering the next week, > all I need to do is add 7 to my start date (making sure that my start > date is correct of course to begin with) and then keep doing this > throughout the loop. > It's amazing what watching 3 episodes of Top Chef in a row can do for > the mind. > Think I got this covered. > :) > Webbiz
>>I've got a logic problem that is causing my gray's to get grayer. >>Coverting DAILY price data of stocks (Date, Open, High, Low, Close) >>into WEEKLY price data is not difficult. Each week ends on Friday, so >>I check for the first day following Friday to start my week, where I >>will assign the OPEN price of that first day to the week's OPEN, then >>check for the Highest High and Lowest Low price. When I come to Friday >>again, I know that the prior week has ended and I then save the HH and >>LL price, as well as the last price CLOSE and start my new week. >>Where I am confusing myself is when I want to create WEEKLY data that >>has an EndofWeek day other than Friday. >>For example, say I have a SUB routine called ConvertToWeekly. >>Public Sub ConvertToWeekly(ByVal DayOfWeek as Long) >>The DayOfWeek will be a value from 1 to 5, where 1 = Mon, 2 = Tue, 3 = >>Wed, with 5 being the highest for Friday. >>Enum DAYS >> Mon = 1 >> Tue >> Wed >> Thu >> Fri >>End Enum >> ConvertToWeekly(DAYS.Wed) >>The routine is to take DAILY data that is in an array called >>DataArray() and convert it to WEEKLY data, where each week ends on >>Wednesday (because of DAYS.Wed). >>The daily data contains no weekends or holidays. >>DataArray() elements are: >>.sDate as String >>.fOpen as Single >>.fHigh as Single >>.fLow as Single >>.fClose as Single >>So what I started out doing is looping through DataArray(zerobased) >>until I came to the first day of my first week. This was determined by >>doing the following: >>Public Sub ConvertToWeekly(ByVal DayOfWeek as Long) >> 'Default to Friday is DayOfWeek out of range >> If DayOfWeek < 1 or DayOfWeek > 5 then DayOfWeek = 5 >> 'This gets the day number of the first day of week >> lFirstDay = DayOfWeek + 1 >> If lFirstDay > 5 Then lFirstDay = 1 >> *** At this point, I know that each week starts with lFirstDay and >>ends with DayOfWeek. Now I need to loop through DataArray and separate >>the data into my unique week data sets. These are stored into another >>array called WeeklyData(). Same structure as DataArray(). >>If my DayOfWeek = 3 for Wednesday, for example, that means my weeks >>will be made up of the following: >> 45123 >>Therefore, I cannot simply use tests like "If WeekDay is >= 4 and <= 3 >>then..." >>So how might I test for the start of each new week and the end of a >>previous week? >>Should I somehow convert my DayOfWeek value within the routine to >>reflect the first day of the week to be 1 using the WeekDay() >>function? >>lDayNum = WeekDay(DataArray(n).sDate, vbTuesday) >>Okay, my head hurts now and I'm going to take a break and watch Top >>Chef. Then keep trying to pan it out. If someone has a suggestion when >>I get back, great. >>Thanks in Advance! >>Webbiz >>(PS: I'm sure there is a simple way to do this. Whenever I ask these >>kinds of questions, someone pops up and says "hey, you know there is a >>function for that, don't you? Thing is, I haven't found it yet. :)

Thu, 15 Mar 2012 00:31:30 GMT 


Webbi #4 / 7

Logic Problem: Convert Daily Data to Unique Weekly
Hi Rick. I appreciate the example code. I'll have to save it for perhaps something in the future. Unfortunately, it wouldn't apply to this particular logic problem. The oneliner requires you specify what you want (2nd Thursday of October), rather than 'give me the first Monday in my dataset, without knowing which Monday of whatever month it is.' To get the start date, what I've done is simply loop through the array from the beginning and test for Monday (or whatever day I'm looking to be the first day of each week in my resultant dataset). Here is how the routine ended up. ======== START CODE =========== Private Sub ConvertToWeekly(ByVal DayOfWeek As Long) 'This routine will convert the DAILY data found in DataArray() 'and covert it to WEEKLY data. DayOfWeek will determine what 'day each week will end. Monday = 1, Tuesday = 2, etc. 'The final results will be stored into the strPrices() array. Dim lStartIndex As Long Dim lEndIndex As Long Dim fHighestHigh As Single Dim fLowestLow As Single Dim fLastClose As Single Dim lFirstDay As Long Dim i As Long Dim lCnt As Long Dim dRefDate As Date 'Make sure DayOfWeek is within proper range. Default to 5 'Friday' if not. If DayOfWeek < 1 Or DayOfWeek > 5 Then DayOfWeek = 5 lFirstDay = DayOfWeek + 1 If lFirstDay > 5 Then lFirstDay = 1 'Locate the first day of the first week to start the loop For i = 0 To UBound(DataArray) If Weekday(DataArray(i).dDate, vbMonday) = lFirstDay Then lStartIndex = i Exit For End If Next i 'Locate the last day of the last complete week For i = UBound(DataArray) To 0 Step 1 If Weekday(DataArray(i).dDate, vbMonday) = DayOfWeek Then lEndIndex = i Exit For End If Next i 'Now, starting from the first day of the first week, start creating 'the new weeks 'Initialize starting values lCnt = 0 ReDim strPrices(lCnt) fHighestHigh = DataArray(lStartIndex).fHigh If DataArray(lStartIndex).fLow <> 0 Then fLowestLow = DataArray(lStartIndex).fLow fLastClose = DataArray(lStartIndex).fClose Else fLowestLow = 99999 End If dRefDate = ForwardDate(DataArray(lStartIndex).dDate, 4) For i = lStartIndex To lEndIndex If DataArray(i).dDate > dRefDate Or i = lEndIndex Then 'moved into following week 'Now time to save the info ReDim Preserve strPrices(lCnt) strPrices(lCnt).dDate = dRefDate 'store end of week date strPrices(lCnt).fHigh = fHighestHigh strPrices(lCnt).fLow = fLowestLow strPrices(lCnt).fClose = fLastClose 'Now reset variables for the new week dRefDate = dRefDate + 7 'increment it to the end of new week fHighestHigh = DataArray(i).fHigh 'start value If DataArray(i).fLow <> 0 Then fLowestLow = DataArray(i).fLow 'start value Else fLowestLow = 99999 End If lCnt = lCnt + 1 'records stored End If If DataArray(i).fHigh > fHighestHigh Then fHighestHigh = DataArray(i).fHigh If DataArray(i).fLow < fLowestLow Then fLowestLow = DataArray(i).fLow If DataArray(i).fClose <> 0 Then fLastClose = DataArray(i).fClose Next i End Sub ============= END CODE =============== Thanks. Webbiz On Sat, 26 Sep 2009 12:31:30 0400, "Rick Rothstein" Quote:
>I think this may help you (if not to calculate all your days, at least to >get you your start date). You can calculate nth such and such day for a >given month and year (that is, for example, the 2nd Thursday in October, >2009) using this oneliner statement... >NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth)  Weekday( _ > DateSerial(Y, M, 8  DOW), vbMonday) >where Y and M are the year and month, DOW is the day of the week (using your >Monday=1, Tuesday=2, etc. schedule) and Nth is which day of the week in the >month you are interested in (Nth = 1 for first such and such day, Nth=2 for >second such and such day, etc.); for example, if you wanted the 2nd Thursday >in October 2009... >Y = 2009 >M = 10 >Nth = 2 >DOW = 4 >NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth)  Weekday( _ > DateSerial(Y, M, 8  DOW), vbMonday)

Fri, 16 Mar 2012 14:32:38 GMT 


Larry Serflate #5 / 7

Logic Problem: Convert Daily Data to Unique Weekly
I'd mention that you can know ahead of time how many weeks are in the data array, and can therefore dimension your output array before doing your looping. ReDimming an array is time consuming, you might as well avoid it, since you can. Here is another approach. If you know that your data is contigious (MonFri) without any gaps, you can use loop counters to track the days of the week. Again if your data is contigious then you know there is always 5 days in every week so you can loop like: For w = 1 to weeks For d = 1 to 5 ' Process week data Next ' Save week hi's and Lo's Next All you need to know is which index of the data array is the first day in the first week and can simply increment that counter in the 1 to 5 loop as you go through all of the data array. Something like the following would also work, but I don't have your data and so did not test it, but see if you can gleen some use out of it in your own code... (Also note I used VB's own day constants) LFS Private Sub CWeekly(ByVal WeekDayEnd As VBA.VbDayOfWeek) Dim DataIndex As Long, Lo As Long, Hi As Long, Ls As Long, Wk As Long, Dy As Long Dim OutIndex As Long, OutMax As Long, Start As Long If (WeekDayEnd < vbMonday) Or (WeekDayEnd > vbFriday) Then WeekDayEnd = vbFriday End If ' Get data start date Start = CLng(DataArray(0).Date) Start = Start  (Start \ 7) * 7 ' (Pseudo Weekday function) ' Assign start index If Start <= WeekDayEnd Then DataIndex = (WeekDayEnd  Start) + 1 Else DataIndex = (WeekDayEnd  Start) + 4 End If ' Verifications Debug.Print Debug.Print "Week ends on: "; Format(WeekDayEnd, "ddd") Debug.Print "Data(0) starts on: "; Format(DataArray(0).Date, "ddd") Debug.Print "First day at index: "; DataIndex OutMax = ((UBound(DataArray)  DataIndex) \ 5)  1 ' Not tested (but can work) Debug.Print "Data Array ("; UBound(DataArray); ") has "; OutMax + 1; " weeks" ' Allocate space ReDim Out(0 To OutMax) As UDT For Wk = 0 To OutMax ' Init weekly hi's & lo's Hi = 1 Lo = 99999 Ls = 0 ' Process week For Dy = 1 To 5 If DataArray(DataIndex).Hi > Hi Then Hi = DataArray(DataIndex).Hi End If If DataArray(DataIndex).Last > 0 Then Ls = DataArray(DataIndex).Last End If If DataArray(DataIndex).Lo > 0 Then If DataArray(DataIndex).Lo < Lo Then Lo = DataArray(DataIndex).Lo End If End If DataIndex = DataIndex + 1 Next ' Dy ' Save hi's & lo's Out(OutIndex).Hi = Hi Out(OutIndex).Lo = Lo Out(OutIndex).Last = Ls Out(OutIndex).Date = DataArray(DataIndex  1).Date OutIndex = OutIndex + 1 Next ' Wk End Sub

Fri, 16 Mar 2012 22:02:43 GMT 


Webbi #6 / 7

Logic Problem: Convert Daily Data to Unique Weekly
On Mon, 28 Sep 2009 09:02:43 0500, "Larry Serflaten" Quote:
>I'd mention that you can know ahead of time how many >weeks are in the data array, and can therefore dimension >your output array before doing your looping. ReDimming >an array is time consuming, you might as well avoid it, since you >can.
Didn't think of that. Thanks. Quote: >Here is another approach. If you know that your data is contigious >(MonFri) without any gaps, you can use loop counters to track >the days of the week. Again if your data is contigious then you >know there is always 5 days in every week so you can loop like: >For w = 1 to weeks > For d = 1 to 5 > ' Process week data > Next > ' Save week hi's and Lo's >Next
Nope. Just checked. It's got gaps. Holidays are not in the data set. That would have been pretty cool though. :) Quote: >All you need to know is which index of the data array is the first >day in the first week and can simply increment that counter in the >1 to 5 loop as you go through all of the data array. >Something like the following would also work, but I don't have your >data and so did not test it, but see if you can gleen some use out of it >in your own code... (Also note I used VB's own day constants) >LFS >Private Sub CWeekly(ByVal WeekDayEnd As VBA.VbDayOfWeek) >Dim DataIndex As Long, Lo As Long, Hi As Long, Ls As Long, Wk As Long, Dy As Long >Dim OutIndex As Long, OutMax As Long, Start As Long > If (WeekDayEnd < vbMonday) Or (WeekDayEnd > vbFriday) Then > WeekDayEnd = vbFriday > End If > ' Get data start date > Start = CLng(DataArray(0).Date) > Start = Start  (Start \ 7) * 7 ' (Pseudo Weekday function) > ' Assign start index > If Start <= WeekDayEnd Then > DataIndex = (WeekDayEnd  Start) + 1 > Else > DataIndex = (WeekDayEnd  Start) + 4 > End If >' Verifications >Debug.Print >Debug.Print "Week ends on: "; Format(WeekDayEnd, "ddd") >Debug.Print "Data(0) starts on: "; Format(DataArray(0).Date, "ddd") >Debug.Print "First day at index: "; DataIndex > OutMax = ((UBound(DataArray)  DataIndex) \ 5)  1 ' Not tested (but can work) >Debug.Print "Data Array ("; UBound(DataArray); ") has "; OutMax + 1; " weeks" > ' Allocate space > ReDim Out(0 To OutMax) As UDT > For Wk = 0 To OutMax > ' Init weekly hi's & lo's > Hi = 1 > Lo = 99999 > Ls = 0 > ' Process week > For Dy = 1 To 5 > If DataArray(DataIndex).Hi > Hi Then > Hi = DataArray(DataIndex).Hi > End If > If DataArray(DataIndex).Last > 0 Then > Ls = DataArray(DataIndex).Last > End If > If DataArray(DataIndex).Lo > 0 Then > If DataArray(DataIndex).Lo < Lo Then > Lo = DataArray(DataIndex).Lo > End If > End If > DataIndex = DataIndex + 1 > Next ' Dy > ' Save hi's & lo's > Out(OutIndex).Hi = Hi > Out(OutIndex).Lo = Lo > Out(OutIndex).Last = Ls > Out(OutIndex).Date = DataArray(DataIndex  1).Date > OutIndex = OutIndex + 1 > Next ' Wk >End Sub

Sat, 17 Mar 2012 05:51:09 GMT 


Rick Rothstei #7 / 7

Logic Problem: Convert Daily Data to Unique Weekly
Obviously, I misread what you were intending to do.  Rick (MVP  Excel)
Quote: > Hi Rick. > I appreciate the example code. I'll have to save it for perhaps > something in the future. Unfortunately, it wouldn't apply to this > particular logic problem. The oneliner requires you specify what you > want (2nd Thursday of October), rather than 'give me the first Monday > in my dataset, without knowing which Monday of whatever month it is.' > To get the start date, what I've done is simply loop through the array > from the beginning and test for Monday (or whatever day I'm looking to > be the first day of each week in my resultant dataset). > Here is how the routine ended up. > ======== START CODE =========== > Private Sub ConvertToWeekly(ByVal DayOfWeek As Long) > 'This routine will convert the DAILY data found in DataArray() > 'and covert it to WEEKLY data. DayOfWeek will determine what > 'day each week will end. Monday = 1, Tuesday = 2, etc. > 'The final results will be stored into the strPrices() array. > Dim lStartIndex As Long > Dim lEndIndex As Long > Dim fHighestHigh As Single > Dim fLowestLow As Single > Dim fLastClose As Single > Dim lFirstDay As Long > Dim i As Long > Dim lCnt As Long > Dim dRefDate As Date > 'Make sure DayOfWeek is within proper range. Default to 5 'Friday' > if not. > If DayOfWeek < 1 Or DayOfWeek > 5 Then DayOfWeek = 5 > lFirstDay = DayOfWeek + 1 > If lFirstDay > 5 Then lFirstDay = 1 > 'Locate the first day of the first week to start the loop > For i = 0 To UBound(DataArray) > If Weekday(DataArray(i).dDate, vbMonday) = lFirstDay Then > lStartIndex = i > Exit For > End If > Next i > 'Locate the last day of the last complete week > For i = UBound(DataArray) To 0 Step 1 > If Weekday(DataArray(i).dDate, vbMonday) = DayOfWeek Then > lEndIndex = i > Exit For > End If > Next i > 'Now, starting from the first day of the first week, start > creating > 'the new weeks > 'Initialize starting values > lCnt = 0 > ReDim strPrices(lCnt) > fHighestHigh = DataArray(lStartIndex).fHigh > If DataArray(lStartIndex).fLow <> 0 Then > fLowestLow = DataArray(lStartIndex).fLow > fLastClose = DataArray(lStartIndex).fClose > Else > fLowestLow = 99999 > End If > dRefDate = ForwardDate(DataArray(lStartIndex).dDate, 4) > For i = lStartIndex To lEndIndex > If DataArray(i).dDate > dRefDate Or i = lEndIndex Then 'moved > into following week > 'Now time to save the info > ReDim Preserve strPrices(lCnt) > strPrices(lCnt).dDate = dRefDate 'store end of week date > strPrices(lCnt).fHigh = fHighestHigh > strPrices(lCnt).fLow = fLowestLow > strPrices(lCnt).fClose = fLastClose > 'Now reset variables for the new week > dRefDate = dRefDate + 7 'increment it to the end of new > week > fHighestHigh = DataArray(i).fHigh 'start value > If DataArray(i).fLow <> 0 Then > fLowestLow = DataArray(i).fLow 'start value > Else > fLowestLow = 99999 > End If > lCnt = lCnt + 1 'records stored > End If > If DataArray(i).fHigh > fHighestHigh Then fHighestHigh = > DataArray(i).fHigh > If DataArray(i).fLow < fLowestLow Then fLowestLow = > DataArray(i).fLow > If DataArray(i).fClose <> 0 Then fLastClose = > DataArray(i).fClose > Next i > End Sub > ============= END CODE =============== > Thanks. > Webbiz > On Sat, 26 Sep 2009 12:31:30 0400, "Rick Rothstein"
>>I think this may help you (if not to calculate all your days, at least to >>get you your start date). You can calculate nth such and such day for a >>given month and year (that is, for example, the 2nd Thursday in October, >>2009) using this oneliner statement... >>NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth)  Weekday( _ >> DateSerial(Y, M, 8  DOW), vbMonday) >>where Y and M are the year and month, DOW is the day of the week (using >>your >>Monday=1, Tuesday=2, etc. schedule) and Nth is which day of the week in >>the >>month you are interested in (Nth = 1 for first such and such day, Nth=2 >>for >>second such and such day, etc.); for example, if you wanted the 2nd >>Thursday >>in October 2009... >>Y = 2009 >>M = 10 >>Nth = 2 >>DOW = 4 >>NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth)  Weekday( _ >> DateSerial(Y, M, 8  DOW), vbMonday)

Sun, 18 Mar 2012 01:42:37 GMT 


