1st, 2nd, 3rd 4th *weekday* of the month

Results 1 to 6 of 6

Thread: 1st, 2nd, 3rd 4th *weekday* of the month

  1. #1

    Default 1st, 2nd, 3rd 4th *weekday* of the month

    Hi guys,<BR>I require assistance with the following code im working on. Its close but no cigar. Infact its probably not even that. <BR><BR>What im trying to achieve (Ill try and keep this simple) is find the 1st, 2nd, 3rd or 4th weekday of a month. Ill show you the code I am currently working on. Ive added notations to explain it as I go through..<BR><BR>counter<BR>*counter is the value of which the user may select for the desired weekday of the month (1,2,3,4)<BR>*if the user wants the value set to the 3rd weekday of the month, the counter is then 3<BR><BR>edate = DateSerial(Year(edate), Month(edate) + iOffset, 1)<BR>*simply the start date at the beginning of the month<BR><BR>do until counter = 1<BR>if (Weekday(edate) = 1 OR Weekday(edate) = 7) then<BR>edate = DateAdd("d", 1, edate)<BR>*if its a saturday or a sunday then add a day and move on. Dont deduct from the counter because its not yet a weekday.<BR><BR>else<BR>edate = DateAdd("d", 1, edate)<BR>counter = counter - 1<BR>*its a weekday so add a day and deduct from the counter<BR><BR>end if<BR>loop<BR><BR>It seems to be working, however on months that start on a friday or weekend day, it throws dates which are incorrect.<BR><BR>Any suggestions guys?<BR><BR>Cheers,<BR>John

  2. #2

    Default anyone..?


  3. #3
    Join Date
    Dec 1969

    Default Lets work this out...

    ...in our heads. You&#039;re saying it&#039;s anything between 1st and 4th weekday of the month, right? Not 5th, 6th, 7th or anything? If so, lets think about this one.<BR><BR>Lets put the counter as the day of the month:<BR><BR>edate = DateSerial(Year(edate), Month(edate) + iOffset, counter)<BR><BR>Now, this day could be a Sunday or Saturday, so we&#039;ll check for that and adjust accordingly:<BR><BR>If Weekday(edate) = 1 Then<BR>&nbsp;&nbsp;edate = DateAdd("d", 1, edate)<BR>Else If Weekday(edate) = 7) Then<BR>&nbsp;&nbsp;edate = DateAdd("d", 2, edate)<BR>End If<BR><BR>That should do the trick.<BR><BR>As I said, it only works for 1st to 5th day of the month - and not above.<BR><BR>Also, I think your logic works, except that you only add one day for the Saturday. You should add two days if it&#039;s a Saturday, just like I do in the code above.<BR><BR>Oliver.

  4. #4
    Join Date
    Dec 1969

    Default No, that's inadequate.

    Let&#039;s say Friday falls on the 1st.<BR><BR>Let&#039;s say the user chose 4 days.<BR><BR>So with your scheme, you&#039;ll choose the 4th. Which is neither a Saturday nor a Sunday, so you&#039;ll just leave it as the 4th!<BR><BR>Whereas it should *actually* be the 6th!<BR><BR>Try again.<BR><BR>

  5. #5
    Join Date
    Dec 1969

    Default Try this...

    &#060;%<BR>count = ...user&#039;s choice of 1 thru 4...<BR>first = DateSerial( y, m, d ) &#039; as you do now<BR><BR>wdFirst = Weekday( first )<BR>&#039; handle Sunday specially:<BR>If wdFirst = vbSunday Then <BR> count = count + 1<BR>Else<BR> test = wdFirst + count - 1<BR> If test &#062; vbFriday Then count = count + 2<BR>End If<BR><BR>&#039; count is now correct day of month<BR>chosenDate = first + count - 1 &#039; if you wish<BR>%&#062;<BR><BR>See if that doesn&#039;t work.<BR><BR>1st on Wednesday (wkday 4), choice is 3. 4+3-1 = 6, which is NOT &#062; 6, so chosenDate is 3rd.<BR><BR>1st on Wednesday (wkday 4), choice is 4. 4+4-1 = 7, which is &#062; 6, so add 2. chosenDate is 6th.<BR><BR>1st on Thursday (wkday 5), choice is 3. 5+3-1 = 7, which is &#062; 6, so add 2. chosenDate is 5th.<BR><BR>1st on Thursday (wkday 5), choice is 4. 5+4-1 = 8, which is &#062; 6, so add 2. chosenDate is 6th.<BR><BR>1st on Friday (wkday 6), choice is 2. 6+2-1 = 7, which is &#062; 6, so add 2. chosenDate is 4th.<BR><BR>1st on Saturday (wkday 7), choice is 1. 7+1-1 = 7, which is &#062; 6, so add 2. chosenDate is 3rd.<BR><BR>******<BR><BR>Could have handled Saturday separately, too, but no need since the adjustment by 2 works fine for it.<BR><BR>

  6. #6

    Default Thanks guys...

    Yes Bill you were right. I infact was having difficulty when the first weekday of the month fell on a friday, saturday or sunday.<BR><BR>Ill give what you have suggested a go tonight.<BR><BR>Thanks again,<BR>John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts