# 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 LongDim StartDate() As VariantDim Date1 As DateDim EndDate As DateDim Days As SingleWith Worksheets("Sheet1")'Determine last Row in Column AlastRow = Sheet1.Range("B999999").End(xlUp).RowDate1 = Cells(2, 6).Value'Calculate Difference between Start Date And End Date in DaysStartDate = Range("A2:A" & lastRow)For i = LBound(StartDate) To UBound(StartDate)EndDate = Cells(2, i).ValueIf StartDate(1, i) < Date1 ThenDays = DateDiff("D", Date1, EndDate)Days = Days + 1Cells(i + 1, 3) = DaysDays = 0ElseIf EndDate < Date1 ThenDays = DateDiff("D", Date1, Date1)Days = Days + 1Cells(i + 1, 3) = DaysDays = 0ElseIf Days = DateDiff("D", StartDate, EndDate) ThenDays = Days + 1Cells(i + 1, 3) = DaysDays = 0End IfNext iEnd WithEnd 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
``````