当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - DateDiff And Summing Dates Into A Calendar Of Dates

问题描述:

|Start Date |End Date |Diff|

|15-Feb-17 |16-Feb-17| 2 |

|13-Feb-17 |13-Feb-17| 1 |

|13-Feb-17 |14-Feb-17| 2 |

|01-Feb-17 |10-Feb-17| 1 | '(When dates are like this then the difference = 1)

|15-Jan-17 |15-Feb-17| 3 | '(This = Difference between End Date and First date in 'the calendar)

In my VBA code below I receive a type mismatch error when trying achieve the above differences. Please assist with this

Sub OnRentCounter()

Dim lastRow As Long

Dim StartDate() As Variant

Dim Date1 As Date

Dim EndDate As Date

Dim Days As Single

With Worksheets("Sheet1")

'Determine last Row in Column A

lastRow = Sheet1.Range("B999999").End(xlUp).Row

Date1 = Cells(2, 6).Value

'Calculate Difference between Start Date And End Date in Days

StartDate = Range("A2:A" & lastRow)

For i = LBound(StartDate) To UBound(StartDate)

EndDate = Cells(2, i).Value

If StartDate(1, i) < Date1 Then

Days = DateDiff("D", Date1, EndDate)

Days = Days + 1

Cells(i + 1, 3) = Days

Days = 0

ElseIf EndDate < Date1 Then

Days = DateDiff("D", Date1, Date1)

Days = Days + 1

Cells(i + 1, 3) = Days

Days = 0

ElseIf Days = DateDiff("D", StartDate, EndDate) Then

Days = Days + 1

Cells(i + 1, 3) = Days

Days = 0

End If

Next i

End With

End Sub

网友答案:

You were using DateDiff("D", StartDate, EndDate) but StartDate is a Variant,

it should be : DateDiff("d", StartDate(i, 1), EndDate)

Sub OnRentCounter()
    Dim LastRow As Long
    Dim StartDate() As Variant
    Dim Date1 As Date
    Dim EndDate As Date
    Dim Days As Single

    With Worksheets("Sheet1")
        'Determine last Row in Column A
         LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
         Date1 = .Cells(2, 6).Value
         'Calculate Difference between Start Date And End Date in Days
         StartDate = .Range("A2:A" & LastRow).Value
         For i = LBound(StartDate, 1) To UBound(StartDate, 1)
             EndDate = .Cells(2, i).Value

             Debug.Print Format(StartDate(i, 1),"dd-MMM-yy")
             Debug.Print Format(EndDate,"dd-MMM-yy")
             Debug.Print Format(Date1,"dd-MMM-yy")


             If StartDate(i, 1) < Date1 Then
                 Days = DateDiff("d", Date1, EndDate)
             ElseIf EndDate < Date1 Then
                 Days = DateDiff("d", Date1, Date1)
             ElseIf Days = DateDiff("d", StartDate(i, 1), EndDate) Then

             End If
             .Cells(i + 1, 3) = Days + 1
             Days = 0
         Next i
     End With
 End Sub
分享给朋友:
您可能感兴趣的文章:
随机阅读: