Excel VBA Substract两个日期来计算整个列中的天数

Question

我正在尝试一个没有积极结果的函数--计算"B“、B2:B栏和今天的日期之间的天差，即单元格"K2”中的日期，并将结果转移到第7栏range: G2:G中。到目前为止，我对VBA编码越来越感兴趣，但作为一个需要更多执行的人，我想寻求一些关于解决这个问题的帮助。

Sub DateSub()
    Dim strInput As String, strOutput As String
    Dim start_date As Date
    Dim end_date As Date
    Dim diff As Integer
    
   Dim LastRowcheck As Long, n1 As Long
    Sheets("T1").Range("K2") = Format(Date, "DD-MM-YY") 'today
    end_date = Sheets("T1").Cells(2, 11).Value
    LastRowcheck = Sheets("T1").Range("B" & Rows.Count).End(xlUp).Row

  For n1 = 1 To LastRowcheck
    With Worksheets("T1").Cells(n1, 2)
        start_date = Sheets("T1").Cells(n1, 2).Value
        diff = DateDiff("D", start_date, end_date) ' HERE IS ISSUE
        Cells(nl + 1, 2) = diff
        Sheets("T1").Cells(n1 + 1, 7).Select
        Selection.NumberFormat = "dd-mm-yy"
        
   End With
  Next n1
    Application.DisplayAlerts = False

End Sub

Answer 1

Sub DateSub()
    Dim strInput As String, strOutput As String
    Dim start_date As Date
    Dim end_date As Date
    Dim diff As Integer
    Dim LastRowcheck As Long
    Dim n1 As Long
    Sheets("T1").Range("K2") = Format(Date, "DD-MM-YYYY") 'today
    end_date = Sheets("T1").Cells(2, 11).Value
    LastRowcheck = Sheets("T1").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("T1").Activate
    For n1 = 2 To LastRowcheck
        start_date = Sheets("T1").Cells(n1, 2).Value
        diff = DateDiff("D", start_date, end_date) 
        Cells(n1, 3) = diff
        Cells(n1 + 1, 7).Select
        Selection.NumberFormat = "dd-mm-yy"
    Next n1
    Application.DisplayAlerts = False
End Sub

以上代码工作良好，并进行了测试。几乎没有什么东西改变和纠正了。

1. 单元格(nl+ 1，2) = diff将用diff覆盖同列中的下一行，这应该移到下一列。
2. 格式(日期，"DD-MM-YY")改为YYYY，以避免19世纪或20世纪的混乱
3. 不需要使用和
4. 我已将for循环更改为从第2行开始，您可以将其更改为1，但请确保它不为空，否则会出现溢出错误。

Answer 2

不确定这是否是您溢出的原因，但是要注意以下几点。特别是使用日期时，如果使用Integer数据类型，则很容易获得溢出错误。

根据VBA数据类型的文档；

Integer 2 bytes -32,768 to 32,767

将diff的数据类型更改为Long - Dim diff As Long。

Long (Long integer) 4 bytes -2,147,483,648 to 2,147,483,647

Answer 3

插入日期值，而不是格式化字符串：

Sheets("T1").Range("K2") = Date 'today

将日期格式应用于K2。