Project date of money running out based of daily expenditure

[DoctorAwe]

Hi,

I spend $10 on weekdays and $5 on Saturdays and always 0 on Sundays.

How can Excel figure out the date that I will have no money left?

If the day I start spending is today (16th Oct 2012) and I have $200 what date will it run out.

 

[tweedle]

If A2...An is the dates
This will give you expenditure per day as copied down next to the dates
=CHOOSE(WEEKDAY(A2),0,10,10,10,10,10,5)

Based on Tuesday, October 16, 2012 Start, Friday, November 09, 2012 you'll be asking someone for a $5 loan.

 

[DoctorAwe]

Very cool. Many thanks,

but is there a way to return the date in the cell without making a calendar?

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)?

 

[tweedle]

I believe that would require a UDF, as below;

*	A	B	C	D	E

<colgroup><col style="width:30px; "><col style="width:56px;"><col style="width:49px;"><col style="width:42px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"] * 200.00 [/TD]
[TD="align: right"] * 10.00 [/TD]
[TD="align: right"] * 5.00 [/TD]
[TD="align: right"]10/16/2012[/TD]
[TD="align: right"]11/10/2012[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E1 =udfDepdate( A1,B1,C1,D1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Public Function udfDepDate(startDollars As Range, wdDollars As Range, satDollars As Range, StartDate As Range)
'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

udfDepDate = "#N/A"

If Not IsDate(StartDate) Then GoTo ExitOther
If Not IsNumeric(startDollars) Then GoTo ExitOther
If Not IsNumeric(wdDollars) Then GoTo ExitOther
If Not IsNumeric(satDollars) Then GoTo ExitOther

curDate = StartDate
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
    adjustment = sunDollars + satDollars + wdDollars
    If doDebug Then Debug.Print Format(curDate, "ddd"), depVal, curVal
    curDate = curDate + 1
    
    DoEvents
Loop

ExitNormal:
    udfDepDate = curDate
    Exit Function

ExitOther:
    udfDepDate = "#N/A"

End Function

 

[DoctorAwe]

Thanks a lot! I think that will work for me.

 

[DoctorAwe]

Thanks again, but shouldn't the end date be November 9th? It works very close to how it should, but does add a couple of extra days for some reason. Any ideas?

 

[tweedle]

It's a bit of an interpretive case between the day the $ is depleted vs the day that is started with no $; however if the day the $ are depleted is the requirement, then the 9th is what it should be.

We can add an escape in the evaluation to exit the routine on the day the $ are depleted.

Public Function udfDepDate(startDollars As Range, wdDollars As Range, satDollars As Range, StartDate As Range)
'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

udfDepDate = "#N/A"

If Not IsDate(StartDate) Then GoTo ExitOther
If Not IsNumeric(startDollars) Then GoTo ExitOther
If Not IsNumeric(wdDollars) Then GoTo ExitOther
If Not IsNumeric(satDollars) Then GoTo ExitOther

curDate = StartDate
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-dd"), depVal, curVal
    
[COLOR=#0000ff]    'Escape on the day the $ are depleted
    If curVal <= 0 Then Exit Do[/COLOR]
    
    curDate = curDate + 1
    
    DoEvents
Loop

ExitNormal:
    udfDepDate = curDate
    Exit Function

ExitOther:
    udfDepDate = "#N/A"

End Function

 

[DoctorAwe]

Oh cool, sorry I wasn't clearer. I was looking for the day of depletion though, so thanks heaps!

Also, If I wanted to do it in reverse and find out the first day I should start spending, if I wanted to run out on a certain date how would that work? Could I make another cell to say either 'start' or 'end' then the next cell with the date (as the current D1). Then have the code first find out if it says 'start' or 'end' then accordingly go forwards or backwards?

 

[tweedle]

Hi;
Below is an example.
We're using the parmDateType to control the incremental step in variable curdate.
Note the change in function name.

*	A	B	C	D	E	F
*
*
*
END
START

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"] * 200.00[/TD]
[TD="align: right"] * 10.00[/TD]
[TD="align: right"] * 5.00[/TD]
[TD="align: right"]10/16/2012[/TD]

[TD="align: right"]11/9/2012[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"] * 200.00[/TD]
[TD="align: right"] * 10.00[/TD]
[TD="align: right"] * 5.00[/TD]
[TD="align: right"]10/16/2012[/TD]

[TD="align: right"]11/9/2012[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"] * 200.00[/TD]
[TD="align: right"] * 10.00[/TD]
[TD="align: right"] * 5.00[/TD]
[TD="align: right"]11/9/2012[/TD]

[TD="align: right"]10/16/2012[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"] * 200.00[/TD]
[TD="align: right"] * 10.00[/TD]
[TD="align: right"] * 5.00[/TD]
[TD="align: right"]11/9/2012[/TD]

[TD="align: right"]10/16/2012[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"] * 200.00[/TD]
[TD="align: right"] * 10.00[/TD]
[TD="align: right"] * 5.00[/TD]
[TD="align: right"]10/16/2012[/TD]

[TD="align: right"]11/9/2012[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F1 =udfDepdate( A1,B1,C1,D1) F2 =udfDepdate2( A2,B2,C2,D2,"START") F3 =udfDepdate2( A3,B3,C3,D3,"END") F4 =udfDepdate2( A4,B4,C4,D4,E4) F5 =udfDepdate2( A5,B5,C5,D5,E5) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Public Function udfDepDate[COLOR=#0000cd]2[/COLOR](startDollars, wdDollars, satDollars, parmDate, [COLOR=#0000cd]parmDateType[/COLOR])
[COLOR=#0000cd]'parmDateType should be "START" or "END"[/COLOR]
'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
[COLOR=#0000cd]Dim vStep As Integer[/COLOR]

udfDepDate2 = "#N/A"
[COLOR=#0000cd]vStep = 100[/COLOR]

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
[COLOR=#0000cd]If UCase(parmDateType) = "START" Then vStep = 1
If UCase(parmDateType) = "END" Then vStep = -1
If vStep > 1 Then GoTo ExitOther[/COLOR]

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 + [COLOR=#0000cd]vStep[/COLOR]
    
    DoEvents
Loop

ExitNormal:
    udfDepDate2 = curDate
    Exit Function

ExitOther:
    udfDepDate2 = "#N/A"

End Function

 

[DoctorAwe]

This is perfect and going to be very useful for me. Thanks so much for putting up with me!

Thanks again tweedle! You rock

Albert Einstein also said something like, 'if you can't explain it in five minutes, you don't understand it well enough'. And tweedle you explain it perfectly succinctly!