Excel VBA Comparing between a given date and (give

Question

I am writing a code to ensure that the data date of a system is not later than 2 business month-end date. For instance, if the job run date for the system is 23/12/2015, the valid data date is 30/10/2015 and 30/11/2015. The dates involved are only business days.

I have this code below:

If DateDiff("m", dataDate, jobRunDate) > 2 Then
    MsgBox "Error in dataDate"
End If

However, I do not know how to find:

1. The last day of the month
2. Compute 2 business month back

Any help will be greatly appreciated. Thanks!

Answer 1

To find the last day of the month, you can find the first day of the next month and subtract a day:

Dim last As Date
Dim current As Date

current = Now
last = DateSerial(Year(current), Month(current), 1) - 1
Debug.Print last

To get the last business day of a month, just subtract days until it falls on a weekday:

Do While Weekday(last, vbMonday) > 5
    last = last - 1
Loop
Debug.Print last

Combining the 2 ideas and extracting it as a function gets you this:

Private Sub Example()

    Debug.Print LastBizDayOfMonth(Year(Now), Month(Now) - 1)
    Debug.Print LastBizDayOfMonth(Year(Now), Month(Now) - 2)

End Sub

Private Function LastBizDayOfMonth(inYear As Integer, inMonth As Integer) As Date
    LastBizDayOfMonth = DateSerial(inYear, inMonth + 1, 1) - 1
    Do While Weekday(LastBizDayOfMonth, vbMonday) > 5
        LastBizDayOfMonth = LastBizDayOfMonth - 1
    Loop
End Function

Answer 2

Here is how to get the last day of a month:

Sub LastDayOfMonth()
    Dim d As Date
    mnth = 12
    yr = 2015
    d = DateSerial(yr, mnth + 1, 0)
    MsgBox d
End Sub

You must then subtract two months from that date. If the result falls on a Saturday or Sunday, you must decide if you want to go forward to the next Monday or backward to the previous Friday.