This code projects a date that $0 will occur if I start spending daily values from an amount. An example would be if I start on October 17th with $200 and spend $10 each weekday and $5 each Saturday following then the return is December 12th.
I would like to add a list to the sheet, of individual dates that should be considered as $0 spent.
For example, if December 3rd 2012 was in the list then the code would return December 13th.
I would like to add a list to the sheet, of individual dates that should be considered as $0 spent.
For example, if December 3rd 2012 was in the list then the code would return December 13th.
Code:
Public Function udfDepDate2(startDollars, wdDollars, satDollars, parmDate, parmDateType)'parmDateType should be "START" or "END"
'Custom calculate value to zero
'This goes in a Module [Alt+F11 Alt+I & M]
'For example, A1=$200 (total money), B1=$10 (weekday dollars spent), C1=$5 (Saturday dollars spent), D1= October 16, 2012 (Start date of spending), E1=**End date** (day when $0 occurs)?
Const sunDollars = 0
Const doDebug = True
Dim curDate As Date
Dim curVal As Long, depVal As Integer
Dim vStep As Integer
udfDepDate2 = "#N/A"
vStep = 100
If Not IsDate(parmDate) Then GoTo ExitOther
If Not IsNumeric(startDollars) Then GoTo ExitOther
If Not IsNumeric(wdDollars) Then GoTo ExitOther
If Not IsNumeric(satDollars) Then GoTo ExitOther
If UCase(parmDateType) = "START" Then vStep = 1
If UCase(parmDateType) = "END" Then vStep = -1
If vStep > 1 Then GoTo ExitOther
curDate = parmDate
curVal = startDollars
Do While curVal > 0
Select Case Weekday(curDate)
Case Is = 1
depVal = sunDollars
curVal = curVal - depVal
Case Is = 7
depVal = satDollars
curVal = curVal - depVal
Case Else
depVal = wdDollars
curVal = curVal - depVal
End Select
If doDebug Then Debug.Print Format(curDate, "ddd-mm/dd"), depVal, curVal
'Escape on the day the $ are depleted
If curVal <= 0 Then Exit Do
curDate = curDate + vStep
DoEvents
Loop
ExitNormal:
udfDepDate2 = curDate
Exit Function
ExitOther:
udfDepDate2 = "#N/A"
End Function