Calculate Number of Months Past a certain Date

[joebb3]

I'm almost ashamed to ask this, but I searched and Can't find it here.

Its a simple thing...

I want to calculate the number of "Payments" due or overdue.

(I'm removing the form information to make it easier to read.)

If ContractInitialPayment > Date Then
    PaymentsDue = 0
Else
    PaymentsDue = DateDiff("M", ContractInitialPayment, Date) + 1
End If

The problem here is:

If today is 4/6/2011 and
ContractInitialPayment = 4/5/2011
then PaymentsDue =1

the script works. But...

If today is 4/6/2011 and
ContractInitialPayment = 3/7/2011
then PaymentsDue =2

the script fails because the second payment wouldn't be due until 4/7/2011

Thank you!
Joe

 

[joebb3]

For info... I finally found this function at the MS Script Repository...

It is a script that got me what I needed.

Function fAgeYMD(startdate As Date, EndDate As Date) As String
'Purpose:   Returns the difference between StartDate and EndDate in full years, months and days
'Coded by:  raskew
'To call:
' ? fAgeYMD(#7/6/54#, #10/3/84#)
'Returns:
' 30 years 2 months 28 days

Dim intHold As Integer
Dim dayHold As Integer

   intHold = Int(DateDiff("m", startdate, EndDate)) + _
             (EndDate < DateSerial(Year(EndDate), Month(EndDate), Day(startdate)))

   If Day(EndDate) < Day(startdate) Then
      dayHold = DateDiff("d", startdate, DateSerial(Year(startdate), Month(startdate) + 1, 0)) + Day(EndDate)
   Else
      dayHold = Day(EndDate) - Day(startdate)
   End If
   
   'fAgeYMD = Int(intHold / 12) & " year" & IIf(Int(intHold / 12) <> 1, "s ", " ") _
             & intHold Mod 12 & " month" & IIf(intHold Mod 12 <> 1, "s ", " ") _
             & LTrim(Str(dayHold)) & " day" & IIf(dayHold <> 1, "s", "")
   fAgeYMD = (intHold Mod 12) + 1

I made the last line change and commented out the authors last line. the "+1" then gives you an incremantal month once the date enters into the next month.

You may close the thread. Thank you!

 

[joebb3]

Opps... The last line should be...

fAgeYMD = intHold + 1

for it to work.

Inthold = number of months

but since it is billing, + had to add a month once the date entered into the next billing month.