Using VBA unable to get the difference between two dates in months and remaining days

Question

In Excel the fucntion DateDiff, also let us used atribute like MD : to find the days left post calculating months, but unable to use in in VBA

Eg

Start Date: 7/8/2020
End Date: 6/3/2021

Answer should be : 10 months 26 days

Please let me know a method to do this using VBA excel

fromDate = TextBox1.Value
Dim toDate As Date
toDate = TextBox2.Value  
diff = DateDiff("m", fromDate, toDate) – To find months
totaldays = DateDiff("d", fromDate, toDate) – To find total days
projected_Date = DateAdd("m", (diff), fromDate) – Adding the month to start date
days_diff_proj = DateDiff("d", projected_Date, toDate) Finding the remaining days                  
TextBox3.Value = CStr(diff) + " Months " + CStr(days_diff_proj) + " Days "
    

Answer 1

i assume that toDate is always larger that fromDate.

Your attemt to first calculate the month difference is good. However, as VBA does rounding, you need to catch the case that the calculated number of months is to to large - in that case do the calculation by reducing the number of monthes by one.

Dim month_diff_proj As Long
month_diff_proj = DateDiff("m", fromDate, toDate)

Dim projected_Date As Date, days_diff_proj As Long
projected_Date = DateAdd("m", month_diff_proj, fromDate)

If projected_Date > toDate Then
    month_diff_proj = month_diff_proj - 1    ' reduce by 1 month 
    projected_Date = DateAdd("m", month_diff_proj, fromDate)
End If
days_diff_proj = DateDiff("d", projected_Date, toDate)

Debug.Print month_diff_proj, days_diff_proj