月结束日期VBA

Question

我需要帮助在VBA中编写一行代码来返回月结束日期。月结束日期的逻辑是这样的。。。

如果一个月的最后一天结束于…

星期日：那个月的最后一天就是上一个星期六。（昨天）

星期一：那个月的最后一天就是上一个星期六。（2天前）

星期二：那个月的最后一天就是前一个星期六。（3天前）

星期三：那么当月的最后一天就是即将到来的星期六（未来3天）

星期四：那个月的最后一天是即将到来的星期六（未来两天）

星期五：那么当月的最后一天就是即将到来的星期六（未来1天）

我当前的代码如下。月份结束日期的格式如下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**")

Answer 1

Weekday（）函数会告诉你一周中的当前一天是什么（Sun=1，Mon=2，等等）；4，那么你想要的日期是几天前的Weekday（）。如果WeekDay（）>=3，那么您希望未来有7-WeekDay（）天。

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

如果你真的只是想要一个表达式，这将起作用：

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))