Project a date of completion

[DoctorAwe]

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.

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

 

[p45cal]

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.

?!?!

For example, if December 3rd 2012 was in the list then the code would return December 13th.

?!?!

Excel Workbook
	A	B	C	D	E
1	Start $	weekday spend	Sat spend	Start Date	Completion date
2	200.00	10.00	5.00	16-Oct-12	09-Nov-12
3				03-Dec-12	27-Dec-12
4				17-Oct-12	10-Nov-12
Sheet

 

[DoctorAwe]

Sorry about the mistakes.

Example should be: if start on **November** 17th , then will end on December 12th.

	A	B	C	D	E	F
1	Start $	Weekday Spend	Sat Spend	Choose to Start or End on Date given	Date Given	Date Returned
2	200	10	5	START	17-Nov-2012	12-Dec-2012
3	200	10	5	END	17-Nov-2012	24-Oct-2012

<tbody>
</tbody>

F2 =udfDepdate2( A2,B2,C2,E2,D2)
F3 =udfDepdate2( A3,B3,C3,E3,D3)


Here is what I would like to add:

	G
1	Dates to consider $0
2	1-Dec-2012
3	1-Nov-2012
	etc.

<tbody>
</tbody>

The entire list of dates would be considered by all of the calculations, not just the one occurring on that row.

 

[p45cal]

Here is what I would like to add:

	G
1	Dates to consider $0
2	1-Dec-2012
3	1-Nov-2012
	etc.

<tbody>
</tbody>

The entire list of dates would be considered by all of the calculations, not just the one occurring on that row.

I'm not at all sure what you want:
What do you want to do with these dates to consider 0$? Where do you want to see results?

 

[DoctorAwe]

Sure.

I hope this similar situation will help make sense of what I'm asking.

Consider the expenditure like wages for days worked. The person working for me would earn, $10 on week days, $5 on Saturdays, and the person doesn't work Sundays or public holidays (both $0). I would like to add a list of "public holiday" dates.

I am calculating the date I will run out of money if I start with $200 to pay the wages. But it is not accurate because it does not consider public holidays.


I would like the results to be in the Date Returned column in place of the current results.

 

[p45cal]

This needs a change to the udf. Here it is with the changes made in red (if I remember them right):

Public Function udfDepDate2(startDollars, wdDollars, satDollars, parmDate, parmDateType[COLOR=#ff0000], TheHolidays[/COLOR])    '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 = False    '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
[COLOR=#ff0000]Hols = TheHolidays.Value[/COLOR]

Do While curVal > 0
    'is date in the TheHolidays range? (gave up fighting with .Find to look for dates!):
    [COLOR=#ff0000]IsAHoliday = False
    For Each dte In Hols
        If dte = curDate Then
            IsAHoliday = True
            Exit For
        End If
    Next dte

    If Not IsAHoliday Then[/COLOR]
        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
  [COLOR=#ff0000]  End If[/COLOR]

    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

The screenshot and formulae:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Start $	weekday spend	Sat spend	Choose toStart or Endon Date given	Start Date	Completion date		Dates to consider $0
2	200.00	10.00	5.00	Start	17-Nov-12	14-Dec-12		Sat 01-Dec-12
3	200.00	10.00	5.00	End	17-Nov-12	24-Oct-12		Mon 19-Nov-12
4	200.00	10.00	5.00	Start	17-Nov-12	14-Dec-12
Sheet

 

[DoctorAwe]

Thank you very much p45cal!
That is exactly what I want.

Thank you again for putting up with my unclear requests!