Get Previous Installment Date (1 Viewer)

[pr2-eugin]

Hello all,

I am having a very bad brain freeze day today. I am unable to lock down dates. I will have to pass to a function a Date and a Term and the result would be the Date that is a previous installment of the passed date and term. The following are the examples.

Date (In)     |    Term (In)    |    Date (Out)
--------------+-----------------+--------------
22/02/2015    |    Monthly      |    22/03/2015
22/02/2015    |    Quarterly    |    22/02/2015
01/01/2015    |    Monthly      |    01/03/2015
24/03/2015    |    Annually     |    24/03/2015

The term value would be either Monthly, Quarterly, Annually. This is the function I currently have, but is not giving me the desired outputs. I am having a funny moment. Could someone help me fix the code please?

Public Function getPrevInst(tmpDate As Date, tmpPeriod As String) As Date
    Dim monthDiff As Integer, modVal As Integer, tmpFuncDate As Date, tmpMonDiff As Long

    Select Case tmpPeriod
        Case "Monthly"
            modVal = 1
        Case "Quarterly"
            modVal = 3
        Case Else
            modVal = 12
    End Select

    monthDiff = DateDiff("m", tmpDate, LstDayPrevMnth(Date))

    tmpMonDiff = IIf(monthDiff > 0, monthDiff - (monthDiff Mod modVal), IIf(monthDiff < 0, 0, 1))

    tmpFuncDate = DateAdd("m", tmpMonDiff, tmpDate)
    
    If tmpFuncDate >= Date Then
        getPrevInst = DateAdd("m", monthDiff, tmpDate)
    Else
        getPrevInst = tmpFuncDate
    End If
End Function

Public Function LstDayPrevMnth(InDate As Date) As Date
    LstDayPrevMnth = DateSerial(Year(InDate), Month(InDate), 0)
End Function

My result is (obviously wrong),

Date (In)     |    Term (In)    |    Date (Out)
--------------+-----------------+--------------
22/02/2015    |    Monthly      |    22/03/2015
22/02/2015    |    Quarterly    |    [COLOR=Red][B]22/03/2015[/B][/COLOR]
01/01/2015    |    Monthly      |    [B][COLOR=Red]01/02/2015[/COLOR][/B]
24/03/2015    |    Annually     |    [COLOR=Red][B]24/02/2015[/B][/COLOR]

If you have a better way of finding the previous installment would also be welcomed. If my code is horribly wrong.

Cross posted at Stack Overflow

 

[plog]

You really lost me in your explanation of what is to be returned. It doesn't seem to jive with your sample data:

"a previous installment of the passed date and term"

Date (In)     |    Term (In)    |    Date (Out)
--------------+-----------------+--------------
22/02/2015    |    Monthly      |    22/03/2015
22/02/2015    |    Quarterly    |    22/02/2015
01/01/2015    |    Monthly      |    01/03/2015
24/03/2015    |    Annually     |    24/03/2015

The 1st & 3rd are both Monthly but the 1st one is 1 one month after the passed value and the 3rd one is 2 months after the passed value. The 2nd & the 4th return the exact same date value you pass.

What you want returned makes absolutely no sense to me. Can you explain better?

 

[pr2-eugin]

Sorry about that plog. Here is another shot (putting it in words).

Peter borrows a loan from me. He plans to repay it Monthly, his first installment being 22 February 2015. If I open his record, I need to see when was the last payment due/paid. So today (24 March 2015), I would have got his first payment 22 February 2015, 22 March 2015 (as 22 March was two days ago). So the last installment date is 22 March 2015.

Tom borrows a loan from me. He plans to repay it Monthly, his first installment being 01 January 2015. If I open his record, I need to see when was the last payment due/paid. So today (24 March 2015), I would have got his first payment 01 January 2015, 01 February 2015 and 01 March 2015 (as 01 March was 23 days ago). So the last installment date is 01 March 2015.

Harry borrows a loan from me. He plans to repay it Quarterly, his first installment being 22 February 2015. If I open his record, I need to see when was the last payment due/paid. So today (24 March 2015), I would have got his first payment 22 March 2015 (that is all, the next payment is not due until 22 May 2015 which has not happened yet). So the last installment date is 22 March 2015.

So in general, if I am to pass a Date (start date) and a term, I should be able to get the recent/last payment date that they are due. I hope this brings a clarity.

EDIT: Date mistyped. Thanks plog.

 

[plog]

I think I have it, but the Harry one is throwing me--I think you just mistyped it. For Harry, the term is Quarterly, the first payment is 22/2/2015, wouldn't that make the next payment 22/5/2015? And the value you want returned 22/2/2015? This is Row #2 from your initial example.

That said, I think I have it, and I think the issue you have is with DateDiff - it's not accurate enough (or doesn't work like expected). Check out what this returns:

DateDiff("m","31/3/2015","1/4/2015")

1 day difference, but because they fall in different months it returns 1.

I would use a while loop and use DateAdd to slowly increase the date until it was passed the current date, then roll it back one unit. Here's the psuedo code I would use:

ret=tmpDate 
    ' return value, by default its start date that it was passed

period = 1, 3 or 12
    ' will be amount of time I add to ret to get new date to test

while ret<Date()    ret = DateAdd("m", period, ret) 
    ' moves ret forward until it passes todays date

ret = DateAdd("m", -1*period, ret)
    ' moves ret back one period to get it to date before today

 

[pr2-eugin]

I think I have it, but the Harry one is throwing me--I think you just mistyped it. For Harry, the term is Quarterly, the first payment is 22/2/2015, wouldn't that make the next payment 22/5/2015? And the value you want returned 22/2/2015? This is Row #2 from your initial example.

You are absolutely right on that. Yes, it was a typo.

That said, I think I have it, and I think the issue you have is with DateDiff - it's not accurate enough (or doesn't work like expected). Check out what this returns:

I think this is the reason, I introduced the Last Day of Previous month logic to make sure this problem does not happen, but it seems that that is what is throwing it up anyway.

I would use a while loop and use DateAdd to slowly increase the date until it was passed the current date, then roll it back one unit.

While might be a bit problematic on older dates, although my DB is not that old, I fear it might be painfully slow when running in a Query for 25000 record with dates older than 8 years. Not a huge problem yet, but still.....

I actually think I have modified the solution, not without your help though.

ret = DateAdd("m", -1*period, ret)
' moves ret back one period to get it to date before today

I implemented this sector in my code, works fine (at least for now)

Public Function getPrevInst(tmpDate As Date, tmpPeriod As String) As Date
    Dim monthDiff As Integer, modVal As Integer
    Dim tmpFuncDate As Date, tmpMonDiff As Long
    
    If tmpDate > Date Then
        getPrevInst = tmpDate
        Exit Function
    End If
    
    Select Case tmpPeriod
        Case "Monthly"
            modVal = 1
        Case "Quarterly"
            modVal = 3
        Case Else
            modVal = 12
    End Select

    monthDiff = DateDiff("m", tmpDate, Date)

    monthDiff = monthDiff - (monthDiff Mod modVal)

    tmpFuncDate = DateAdd("m", monthDiff, tmpDate)
    
    If tmpFuncDate > Date Then
        getPrevInst = DateAdd("m", -1 * modVal, tmpFuncDate)
    Else
        getPrevInst = tmpFuncDate
    End If
End Function

 

[Solo712]

You are absolutely right on that. Yes, it was a typo.
I think this is the reason, I introduced the Last Day of Previous month logic to make sure this problem does not happen, but it seems that that is what is throwing it up anyway. While might be a bit problematic on older dates, although my DB is not that old, I fear it might be painfully slow when running in a Query for 25000 record with dates older than 8 years. Not a huge problem yet, but still.....

I actually think I have modified the solution, not without your help though.
I implemented this sector in my code, works fine (at least for now)

Public Function getPrevInst(tmpDate As Date, tmpPeriod As String) As Date
    Dim monthDiff As Integer, modVal As Integer
    Dim tmpFuncDate As Date, tmpMonDiff As Long
 
    If tmpDate > Date Then
        getPrevInst = tmpDate
        Exit Function
    End If
 
    Select Case tmpPeriod
        Case "Monthly"
            modVal = 1
        Case "Quarterly"
            modVal = 3
        Case Else
            modVal = 12
    End Select
 
   [COLOR=red]monthDiff = DateDiff("m", tmpDate, Date)[/COLOR]
 
[COLOR=red]  monthDiff = monthDiff - (monthDiff Mod modVal)[/COLOR]
 
[COLOR=red]  tmpFuncDate = DateAdd("m", monthDiff, tmpDate)[/COLOR]
 
    If tmpFuncDate > Date Then
        getPrevInst = DateAdd("m", -1 * modVal, tmpFuncDate)
    Else
        getPrevInst = tmpFuncDate
    End If
End Function

Hey Paul,
try changing the three red lines above for the two below and see how far you get:

monthDiff = DateDiff("m", tmpDate, Date)
tmpFuncDate = DateAdd("m", modVal * (monthDiff \ modVal), tmpDate)

Best,
Jiri