Excel VBA在给定日期和（给定日期-2个月）之间进行比较 [英] Excel VBA Comparing between a given date and (given date - 2 months)

问题描述

我正在编写代码，以确保系统的数据日期不晚于2个工作月的结束日期。例如，如果系统的作业运行日期为23/12/2015，则有效数据日期为30/10/2015和30/11/2015。涉及的日期仅是工作日。

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.

我下面是这段代码：

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

不知道如何查找：

1. 该月的最后一天


2. 计算2个工作月返回

任何帮助将不胜感激。谢谢！

Any help will be greatly appreciated. Thanks!

推荐答案

要查找该月的最后一天，可以找到下个月的第一天并减去一天： / p>

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

这篇关于Excel VBA在给定日期和（给定日期-2个月）之间进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！