Search code examples
vbadatetimedatediff

Using DateDiff() in VB to find the difference between 2 dates in months and days

I need to find the difference between 2 dates in months and days in VB. Using DateDiff() function, I am not getting the desired result since DateDiff() subtracts the corresponding values only. For example:

DateDiff("m", '31/01/2012', '1/02/2012')

Returns 1 month, which is incorrect. How do I achieve this?

Solution

  • I have solved this. I had to write a function that does what I need. The algorithm is :

    1) Use DateDiff to calculate the difference in months between 2 days. 2) Store the 'dd' part of both the dates in 2 different variables. 3) If 'dd' of start date is greater than 'dd' of end date, month will be Step1.value-1, else, month will be step1.value 4) Increment the Start date by the value of months we get from step 3 5) Run a loop from 1 to 31 incrementing start date by 1 until it is equal to end date. 6) value of days will be (no. of iterations)

    The code is :

    Dim temp As Date
Dim temp1 As Integer
Dim i As Integer
Dim day1 As Integer
Dim day2 As Integer


 temp1 = DateDiff("m", StartDate, EndDate)

 day1 = DatePart("d", StartDate)
 day2 = DatePart("d", EndDate)
 If day1 > day2 Then
    temp = DateAdd("m", (temp1 - 1), StartDate)
    Month = (temp1 - 1)
Else
    temp = DateAdd("m", (temp1), StartDate)
    Month = (temp1)
End If

For i = 1 To 31
    If temp = EndDate Then Exit For
    temp = DateAdd("d", 1, temp)
Next i

Day = (i - 1)