First Workday of month/Last Workday of Month 4

[zarkon4]

Just wondering if anyone has done this before.
I am trying to calculate the first Work day
(Work day being Mon-Fri) of a month and the last workday
of a month.

This is for a scheduling project.

 

[steveouthwaite]

Weekday(date,[first day of week]) will let you determine the day

e.g. weekday(#1/1/2004#,0) = 5 (Thursday)

You could have a simple loop to check the 1st, 2nd, 3rd etc until you find a Monday,
similarly if you start from the 1st of the next month and work backwards, you can find the last Friday

Hope this helps
SteveO

 

[BrianLW]

Try this:
[tt]

Private Function GetFirstMonday(intMonth As Integer, intYear As Integer) As Date
Dim intDay As Integer
Dim intTmp As Integer
Dim dtTemp As Date

Select Case intMonth
Case 1, 3, 5, 7, 8, 10, 12
intDay = 31
Case 4, 6, 9, 11
intDay = 30
Case 2
If (intYear Mod 4) = 0 Then
If (intYear Mod 100) = 0 Then
If (intYear Mod 400) = 0 Then
' Not Leap Year
intDay = 28
Else
intDay = 29
End If
Else
intDay = 29
End If
Else
intDay = 28
End If
End Select

For intTmp = 1 To intDay
dtTemp = CDate(intMonth & "/" & intTmp & "/" & intYear)

If DatePart(&quot;w&quot;, dtTemp) = vbMonday And intTmp < 8 Then
GetFirstMonday = dtTemp
End If

Next intTmp

End Function

Private Function GetLastFriday(intMonth As Integer, intYear As Integer) As Date
Dim intDay As Integer
Dim intTmp As Integer
Dim dtTemp As Date

Select Case intMonth
Case 1, 3, 5, 7, 8, 10, 12
intDay = 31
Case 4, 6, 9, 11
intDay = 30
Case 2
If (intYear Mod 4) = 0 Then
If (intYear Mod 100) = 0 Then
If (intYear Mod 400) = 0 Then
' Not Leap Year
intDay = 28
Else
intDay = 29
End If
Else
intDay = 29
End If
Else
intDay = 28
End If
End Select

For intTmp = intDay To 20 Step -1
dtTemp = CDate(intMonth & &quot;/&quot; & intTmp & &quot;/&quot; & intYear)

If DatePart(&quot;w&quot;, dtTemp) = vbFriday And intTmp > 21 Then
GetLastFriday = dtTemp
End If

Next intTmp

End Function
[/tt]

- B

 

[zarkon4]

Thanks all but I figured it out.

Dim iDayofWeek As Integer
Dim sAdjDate As String
Dim sLastWorkDay As String
Dim sFirstWorkday As String

If Day(Now) > 27 Then
'calc next last workday of month
iDayofWeek = Weekday(DateAdd(&quot;m&quot;, Month(Now) - 1, &quot;01/31/&quot; & Year(Now)))
sAdjDate = DateAdd(&quot;m&quot;, Month(Now) - 1, &quot;01/31/&quot; & Year(Now))
If iDayofWeek = 1 Then
CalcNextRun = DateAdd(&quot;d&quot;, -2, sAdjDate)
ElseIf iDayofWeek = 7 Then
CalcNextRun = DateAdd(&quot;d&quot;, -1, sAdjDate)
End If
Else
'calc next first work day of month
iDayofWeek = Weekday(DateAdd(&quot;m&quot;, Month(Now) - 1, &quot;01/01/&quot; & Year(Now)))
sAdjDate = DateAdd(&quot;m&quot;, Month(Now) - 1, &quot;01/01/&quot; & Year(Now))
If iDayofWeek = 1 Then
CalcNextRun = DateAdd(&quot;d&quot;, 1, sAdjDate)
ElseIf iDayofWeek = 7 Then
CalcNextRun = DateAdd(&quot;d&quot;, 2, sAdjDate)
End If
End If

 

[LostInCode]

TheDate=Date

SearchDate=DateSerial(Year(TheDate),Month(TheDate),1)
x = WeekDay(SearchDate,vbMonday)
If x = 6 then
SearchDate = SearchDate + 2
ElseIf x = 7 then
SearchDate = SearchDate + 1
End If


SearchDate=DateSerial(Year(TheDate),Month(TheDate)+1,0)
x = WeekDay(SearchDate,vbMonday)
If x = 7 then
SearchDate = SearchDate - 2
ElseIf x = 6 then
SearchDate = SearchDate - 1
End If

 

[MichaelRed]

While I applaud the initiative of continuing to work on your soloution, I would like to offer a minor variation on the theme:

Public Function basMnthWrkDay(Optional dtIn As Variant, Optional StrtOrEnd As Variant) As Date

    Dim intDOW As Integer
    Dim dtMyDt As Date
    Dim dtDOMnth As Date
    Dim blnSOE As Boolean

    'Check for a date input.  If none, assumme todays's date
    If (IsMissing(dtIn)) Then
        dtMyDt = Date
     Else
        dtMyDt = dtIn
    End If

    'Check wheather to return the start date or the end date _
     Note: NO entry will be the start (default).  ANY entry will be End
    If (IsMissing(StrtOrEnd)) Then
        blnSOE = True
     Else
        blnSOE = False
    End If

    If (blnSOE = False) Then
        'calc next last workday of month
        dtDOMnth = DateSerial(Year(dtMyDt), Month(dtMyDt) + 1, 0)
        intDOW = Weekday(dtDOMnth)
        If intDOW = 1 Then
            basMnthWrkDay = DateAdd("d", -2, dtDOMnth)
        ElseIf intDOW = 7 Then
            basMnthWrkDay = DateAdd("d", -1, dtDOMnth)
        End If
    Else
        'calc next first work day of month
        dtDOMnth = DateSerial(Year(dtMyDt), Month(dtMyDt), 1)
        intDOW = Weekday(dtDOMnth)
        If intDOW = 1 Then
            basMnthWrkDay = DateAdd("d", 1, dtDOMnth)
        ElseIf intDOW = 7 Then
            basMnthWrkDay = DateAdd("d", 2, dtDOMnth)
        End If
    End If
 
End Function
[code]




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[LostInCode]

A complete solution with the checks.

The meat of your code looks almost like mine (I just use a different weekday factor, but the results are the same).

(BTW, just for the sake of mentioning it, you do not need the DateAdd() part if the variables holding the dates are declared as dates. Just add/subtract the number from the date variable).

 

[strongm]

There's some further illumination on this sort of thing in this thread from last year, Thread222-360830, and the thread it links to from two years ago, Thread222-147546

 

[rdroske]

Whoa, all going a bit overboard I think.

Assuming you mean whether first and last workday is a Monday or Tuesday etc then all you need is

'Start with any date
Date = &quot;2/7/2007&quot;
'Assume First and Last Day of that Month Are Work Days
FirstWorkDate = Date - Day(Date) + 1
LastWorkDate = DateAdd(&quot;m&quot;, 1, Date - Day(Date))
'Then get the correct ones
While (Weekday(FirstWorkDate) = 0) Or (Weekday(FirstWorkDate) = 1)
FirstWorkDate = FirstWorkDate + 1
Wend
While (Weekday(LastWorkDate) = 0) Or (WeekDay(LastWorkDate) = 1)
LastWorkDate = LastWorkDate - 1
Wend
FirstWorkDay = Weekday(FirstWorkDate)
LastWorkDay = Weekday(LastWorkDate)

 

[strongm]

If only that worked properly...

You need to think about this line:

LastWorkDate = DateAdd(&quot;m&quot;, 1, Date - Day(Date))

 

[MichaelRed]

Actually, the entire thread is far form 'overboard' (with the possible exception of the references from strongm. Even those which are 'correct' in terms of finding the first and/or last weekdays (including mine) never even consider the possability that the date may be a holiday, and thus NOT the &quot;required&quot; date - except in the NARROW sense provided in the original post.

I would also question the implementation of a loop structure with the overhead in olace of a simple conditional. In the trivial case of just a couple of iterations, it is not going to be noticable (to the user), but seems much more like 'overboard' than the other posts. While the 'challenge' of a critique is welcome, It would, perhaps, be somewhat more compelling as an apples to apples comparision if it were Workable Code Workable Code vs Workable Code to Pseudo Code.

So, taking some liberties:

Public Function basStDtEndDt(Optional dtIn As Variant, Optional StOrEnd As Variant) As Date

    'Michael Red 2/10/2004  Tek-Tips thread222-771062 _
     Adapted from Pseudo Code posted by rdroske

    Dim rtnWkDay As Date    'Added Declarations as 'Standard' of good practice
    Dim MyDt As Date
    Dim MyRtn As String
    Dim MyIntvl As Integer
    Dim MyWkDay As Integer

    If (IsMissing(dtIn)) Then       'Added Checking of Input
        MyDt = Date
    End If

    If (IsDate(dtIn)) Then
        MyDt = dtIn
     Else
        MyDt = Date
    End If

    If (Not IsMissing(StOrEnd)) Then
        MyRtn = Left(StOrEnd, 1)
    End If

    If (MyRtn <> &quot;E&quot;) Then
        MyRtn = &quot;S&quot;
    End If

    If (MyRtn = &quot;S&quot;) Then               'Return Start
        rtnWkDay = MyDt - Day(Date) + 1
        MyIntvl = 1
        MyWkDay = vbMonday
     Else
        rtnWkDay = DateSerial(Year(MyDt), Month(MyDt) + 1, 0)
        MyIntvl = -1
        MyWkDay = vbFriday
    End If

    'Correct To proper &quot;WorkDay&quot;
    While (Weekday(rtnWkDay) <> MyWkDay)
      rtnWkDay = rtnWkDay + MyIntvl
    Wend

    basStDtEndDt = rtnWkDay
 
End Function

Which - execpting the input checking, is not so different than other soloutions already provided, at least from my perspective.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[rdroske]

Sorry, you're right strongm. Just happened to work anyways for the date I picked. Make it

LastWorkDate = DateAdd(&quot;m&quot;, 1, Date - Day(Date) + 1)

And it does find first and last WEEKDAYS.

Pseudo Code????? I don't know what yours is supposed to do but if I call that function with say 3/5/06 it returns 3/6/06. Whats that?

Using long stupid names doesn't mean its good code bro. I also notice a loop in you code and in case you don't know it just because you only type one line doesn't mean the function you call doesn't have several.

Simple loops are not &quot;overhead&quot;.

As far as declaring variable goes I do also, this wasn't a full program, just the important part.

Holidays would be different for every company and would have to handled with a table.

 

[zarkon4]

Whoa, guys! I have figured it out and my code I posted is working beautifully, I don't care about holidays. When I posted I was having a brain fart, but after expelling the extra air in my head, (and it was Monday afterall) The gears started to turn and I figured it out.
But since I have your attention,
What would be the best way to execute a program from within a VB service. Should I use the standard shell command or use the ShellExecute API call?

 

[strongm]

rdroske,

Don't worry about MichaelRed; he sometimes says some somewhat odd things - but sometimes[/b] they provide important insights or, at least, a valid alternative viewpoint. So there's probably no need to get too worked up about his criticisms.

 

[rdroske]

I'm not really, just couldn't let the pseudo code remark slide totally. I aggree and don't mind criticism, especially when it points out a real bug like yours.

Intereting question on a shell from a service. Did you post that somewhere?

I'm guessing that the ShellExecute API would do pretty much the same thing as a regular shell command...

The issue would be that either way you are not going to get control back to your service until whatever you shell out to completes.

If thats a problem seems like you are looking at spawning a seperate process.

I've done a few services using NTSvc control but never had to shell out...

 

[BobJacksonNCI]

Hi,

Just stopped by to see what all the fuss was about...

And began wondering if Picasso ever argued with Rembrandt about the &quot;best&quot; way to present their work...

I'm so old that when I started, the word was often mistakenly spelled come-putter!

After ad nauseum experience in assembler, NEAT/3, RPG, COBOL and now learning Visual Basic, you'll have a hard time getting me to change my lengthy list of priorities:

1. Does it give correct results?

2. Can someone in the office maintain it while I'm on vacation?



HTH,
Bob