Excel Date calculation

[wDanHoward]

I am trying to calculate the amount months and days between to dates. The DateDiff function will give me the number of months or the number of days but not months and days. Is there a function in VBA or VB that does this?

 

[SkipVought]

Hi,

DateDiff is not a builtin function that I can find.

If the difference is 3 months and 30 days, what do you expect to see in the target cell? Skip,
metzgsk@voughtaircraft.com

 

[wDanHoward]

The DateDiff function is a VBA function. I am using it in a user form to calculate the number. I have to multiply the months and days a payment is late by .01 in order to calculate a penalty. So if the last month had 31 days it would read 3.97 months.

 

[SkipVought]

How about this? ...

Function DateDiff1(Dat1 As Date, Dat2 As Date) As Single
    Dim iYr As Integer, iMo As Integer, iDa As Integer, D1 As Date, D2 As Date, iDaMx As Integer
    If Dat1 > Dat2 Then
        D2 = Dat1
        D1 = Dat2
    Else
        D1 = Dat1
        D2 = Dat2
    End If
    iYr = Year(D2) - Year(D1)
    iMo = Month(D2) - Month(D1)
    Select Case Month(D2)
        Case 12
            iDaMx = 31
        Case Else
            iDaMx = Day(DateSerial(Year(D2), Month(D2) + 1, 1) - 1)
    End Select
    iDa = Day(D2)
    DateDiff1 = iYr * 12 + iMo + iDa / iDaMx

End Function

See if this will work for you Skip,
metzgsk@voughtaircraft.com