Month Ending Date VBA

Question

I need help writing one line of code in VBA to return the Month-Ending date. The logic for Month-Ending date works like this...

If the last day of the month ends on...

Sunday: Then last day of that month is that previous Saturday. (Yesterday)

Monday: Then last day of that month is that previous Saturday. (2 days ago)

Tuesday: Then last day of that month is that previous Saturday. (3 days ago)

Wednesday:Then last day of that month is the upcoming Saturday (3 days in the future)

Thursday: Then last day of that month is the upcoming Saturday (2 days in future)

Friday: Then last day of that month is the upcoming Saturday (1 day in future)

My current code is below. The formatting of the Month-Ending date is as follows. 2016-07-02

Sub Macro1()
With ActiveWorkbook.Connections("ABC Query").ODBCConnection
    .BackgroundQuery = True
    .CommandText = Array( _
    "exec [dbo].[getBSC_Monthly] @MonthEndDate = **where I need the line of code**")

Possible duplicate of Excel VBA Comparing between a given date and (given date - 2 months) — Comintern
It might be somewhat of a duplicate but I'm still confused by it. Working on trying to understand it — Davey

JefferMC JefferMC · Accepted Answer · 2016-07-27T21:54:11

The Weekday() function will tell you what the current day of the week is (Sun=1, Mon=2, etc.). So, if Weekday() < 4, then you want the date Weekday() days ago. If WeekDay() >= 3, then you want 7 - Weekday() days in the future.

Function MonthEnd(d)
    Dim actualmonthend, dow, ans
    actualmonthend = DateSerial(Year(d), Month(d) + 1, 1) - 1
    dow = Weekday(actualmonthend)
    If (dow < 4) Then
        ans = actualmonthend - dow
    Else
        ans = actualmonthend + (7 - dow)
    End If
    MonthEnd = ans
End Function

if you really just want an expression this would work:

DateSerial(Year(d), Month(d) + 1, 1) - 1 - Weekday(DateSerial(Year(d), Month(d) + 1, 1) - 1) + (7 * Abs(Weekday(DateSerial(Year(d), Month(d) + 1, 1) - 1) >= 4))

Month Ending Date VBA

1 Answers