pr2-eugin
Super Moderator
- Local time
- Today, 05:53
- Joined
- Nov 30, 2011
- Messages
- 8,494
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.
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?
My result is (obviously wrong),
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
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.
Code:
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
Code:
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
Code:
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]
Cross posted at Stack Overflow
Last edited: