Calculate the last working day of current month

[JohnLee]

Hi Folks,

I'm hoping someone can help me, I've searched this forum, but haven't been able to find anything that matches what I'm trying to do.

My VB skills are limited, but I'm trying to improve them.

I need to write code that identifies the last working day of the current month and then once that is identified print a report.

This is what I have put together so far:

START CODE:

Dim CurDate
Dim MyWeekDay
Dim DayOfWeek
Dim EndOfMonth

CurDate = Format(Now(), "dd/mm/yy")
MyWeekDay = Weekday(CurDate)

If MyWeekDay = 1 Then
DayOfWeek = "Sunday"
End If

If MyWeekDay = 2 Then
DayOfWeek = "Monday"
End If

If MyWeekDay = 3 Then
DayOfWeek = "Tueday"
End If

If MyWeekDay = 4 Then
DayOfWeek = "Wednesday"
End If

If MyWeekDay = 5 Then
DayOfWeek = "Thursday"
End If

If MyWeekDay = 6 Then
DayOfWeek = "Friday"
End If

If MyWeekDay = 7 Then
DayOfWeek = "Saturday"
End If

END CODE

What I have not been able to work out is how to identify the last working day of the current month!

Any assistance would be most appreciated.

John

 

[raskew]

Hi -

Try placing this in a standard module -- call as shown.

Public Function LastWorkingDay(mmyyyy As String) As Date
'Calls the last working day (Monday - Friday) of a
'specified month/year
'Input: ? LastWorkingDay("062007")
'Output: 6/29/2007
'coded by: raskew

Dim DteHold As Date

   DteHold = DateSerial(Mid(mmyyyy, 3), Left(mmyyyy, 2), 1)
   DteHold = DateAdd("m", 1, DteHold) - 1
   Do Until InStr("17", WeekDay(DteHold, 1)) = 0
      DteHold = DteHold - 1
   Loop
   LastWorkingDay = DteHold
End Function

HTH - Bob

 

[JohnLee]

Hi,

Thanks for your reply, I placed you code as shown into a module of its own and called it modLastWorkingDay and tried to call it from my time module as follows "Call modLastWorkingDay" and got a compile error: Expected variable or procedure, not a Module" message.

any suggestions.

John

 

[RuralGuy]

It is a little too specific to your request but maybe this function will help you.

Public Function LastWorkDay() As Date
'-- Return the last working day of the current month

   Dim Searching As Boolean
   Searching = True

   LastWorkDay = DateSerial(Year(Date), Month(Date) + 1, 0)   '-- Start at the last day of the month
   Do While Searching
      If Weekday(LastWorkDay, vbMonday) > 5 Then
         '-- Weekend day, back up a day
         LastWorkDay = LastWorkDay - 1
      Else
         '-- If you have a Holiday Table then enable the next IF...Else
'         If Weekday(LastWorkday, vbMonday) > 5 Or _
            Not IsNull(DLookup("[HoliDate]", "tblHolidays", _
                               "[HoliDate] = " & Format(LastWorkday, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
            '-- The above Format of LastWorkday works with US or UK dates!
'            LastWorkday = LastWorkday - 1
'         Else
            '-- The search is over
            Searching = False
         End If
      Loop

End Function

 

[JohnLee]

Hi RuralGuy,

Thanks for that, it worked no problem

and Thanks to raskew for responding too.

FAB

John

 

[RuralGuy]

For the record, Bob supplied a Function with directions on you how to use it in the header. (You don't "call" a function) You use the Function name and not the Module name when referencing the Function.