Spreading costs over months or as one off payments

P

panesai1

New Member
Joined
Dec 15, 2015
Messages
18
Hi,

I have an XL sheet which tracks my regular monthly and annual (one-off payment) expenses. (see below).

For monthly costs (eg. Electricity) i need a formula to enter the £42.00 in each of the corresponding months between the start/end dates (F2 to I2)
However, if the expense is a one-off annual payment, (i.e. the Monthly cost column is blank, e.g. Car insurance), I want the formula to place the corresponding cost only once in the corresponding month that the annual payment occured/will occur. In the case of car insurance, the £500 should appear once only in the Jan 2016 column (H4).

I have seen may forum posts that show the correct formula for spreading an annual cost over the period of a start /stop date (for instance if a cost of £12,000 was to be evenly spread out as 12*£1000 costs from Jan - Dec), but I have not seen any posts regarding my issue?

Hope someone can help?

Many thanks
Indy

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]per month[/TD]
[TD]per annum[/TD]
[TD]start date[/TD]
[TD]end date[/TD]
[TD]Nov 2015[/TD]
[TD]Dec 2015[/TD]
[TD]Jan 2016[/TD]
[TD]Feb 2016[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Electricity[/TD]
[TD]£42.00[/TD]
[TD][/TD]
[TD]4/11/13[/TD]
[TD]7/7/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Council tax[/TD]
[TD]£103.00[/TD]
[TD][/TD]
[TD]8/5/14[/TD]
[TD]12/7/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Car insurance[/TD]
[TD][/TD]
[TD]£500.00[/TD]
[TD]3/1/16[/TD]
[TD]2/1/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Car road tax[/TD]
[TD][/TD]
[TD]£180.00[/TD]
[TD]1/6/15[/TD]
[TD]31/5/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Internet bill[/TD]
[TD]£10.00[/TD]
[TD][/TD]
[TD]1/9/15[/TD]
[TD]31/3/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
If I understand correctly, this should meet your needs. Just copy the formulas in row two down as far as necessary.

ABCDEFGHI
per monthper annumstart dateend date
Electricity
Council tax
Car insurance
Car road tax
Internet bill

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]Nov-15[/TD]
[TD="align: right"]Dec-15[/TD]
[TD="align: right"]Jan-16[/TD]
[TD="align: right"]Feb-16[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$42.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]November 4, 2013[/TD]
[TD="align: right"]July 7, 2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$103.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]May 8, 2014[/TD]
[TD="align: right"]July 12, 2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$500.00[/TD]
[TD="align: right"]January 3, 2016[/TD]
[TD="align: right"]January 2, 2017[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]500[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$180.00[/TD]
[TD="align: right"]June 1, 2015[/TD]
[TD="align: right"]May 31, 2016[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$10.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]September 1, 2015[/TD]
[TD="align: right"]March 31, 2017[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]

</tbody>
Sheet24​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(F$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=F$1,$E2>=F$1),$B2,""))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(G$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=G$1,$E2>=G$1),$B2,""))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(H$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=H$1,$E2>=H$1),$B2,""))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(I$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=I$1,$E2>=I$1),$B2,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
@DRSteele: Thank you so much !!! This is absolutely fantastic. Just tried it and it works a treat. Now I'm gonna go away, lock myself in a room and try to figure out how you've pieced it all together. Thanks again!

Indy



DRSteele said:
If I understand correctly, this should meet your needs. Just copy the formulas in row two down as far as necessary.

ABCDEFGHI
per monthper annumstart dateend date
Electricity
Council tax
Car insurance
Car road tax
Internet bill

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]Nov-15[/TD]
[TD="align: right"]Dec-15[/TD]
[TD="align: right"]Jan-16[/TD]
[TD="align: right"]Feb-16[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$42.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]November 4, 2013[/TD]
[TD="align: right"]July 7, 2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$103.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]May 8, 2014[/TD]
[TD="align: right"]July 12, 2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$500.00[/TD]
[TD="align: right"]January 3, 2016[/TD]
[TD="align: right"]January 2, 2017[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]500[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$180.00[/TD]
[TD="align: right"]June 1, 2015[/TD]
[TD="align: right"]May 31, 2016[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$10.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]September 1, 2015[/TD]
[TD="align: right"]March 31, 2017[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]

</tbody>
Sheet24​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(F$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=F$1,$E2>=F$1),$B2,""))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(G$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=G$1,$E2>=G$1),$B2,""))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(H$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=H$1,$E2>=H$1),$B2,""))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(I$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=I$1,$E2>=I$1),$B2,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Oh good show!

The formula looks to see whether or not there is a number in Per Annum as well as whether or not the month of Start Date matches the month of the current column; if so it reports the per Annum number, otherwise putting something else because of the FALSE. The second IF (which is triggered when the first IF reports a FALSE) looks to see whether or not there is a number in the Per Month column as well as whether or not the months of Start Date and End Date bookend the month of the current column; if so, it reports the Per Month Number, otherwise reporting a blank (or "" in Excel).
 
Upvote 0
DRSteele said:
Oh good show!

The formula looks to see whether or not there is a number in Per Annum as well as whether or not the month of Start Date matches the month of the current column; if so it reports the per Annum number, otherwise putting something else because of the FALSE. The second IF (which is triggered when the first IF reports a FALSE) looks to see whether or not there is a number in the Per Month column as well as whether or not the months of Start Date and End Date bookend the month of the current column; if so, it reports the Per Month Number, otherwise reporting a blank (or "" in Excel).
Click to expand...



Hi DRSteele: This is very very helpful for a novice/learner such as myself. Thanks again for everything !

Indy
 
Upvote 0
Hi again...

Sorry, to re-bump my own thread when I had thought it was all resolved.... but I just noticed two nuances....


  1. The following seems to happen for whichever month is used as start date:
    • When there is a cost in the "per month" column, and start date is the first day of the month (eg. 01-Jan-2016), the cost is correctly displayed in the Jan 2016 column.
    • But, when there is a cost in the "per month" column, and start date is NOT the first day of the month (eg. 02-31 Jan 2016), the cost is NOT displayed in the Jan 2016 column. (but does display in correctly subsequent months, provided that the end date has been set sufficiently in to the future. So in this case, the costs are displayed from Feb 2016 onwards)
  2. When there is a cost in the annual column (eg. £50), and a Start and End date are entered (04-Jul-2016 start , end 04-Jul-2025), the cost is correctly displayed only in the Jul-2016 column, but does not repeat in subsequent years (as per end date, it should repeat every July until year 2025).

Any ideas?

Many thanks in advance

Indy
 
Upvote 0
It's not a "re-bump" --- the problem has not been solved, so post away until we're done. I've invented some more data to help us to get to the bottom of this.

I fixed my oversight in point 2; I stupidly thought it was a one-time payment. Now the formula will report a figure when a per annum payment month matches the month in the column and the year in the column is bookended by the payment start and end years. See below.

I don't quite follow what you mean in point 1; are you suggesting that my 37 payment on Dec 17/15 should show up in the Dec 1/15 column? Note that the dates in Row1 are all the first day of the month. Could you please clarify point 1?

ABCDEFGHIJKLM
per monthper annumstart dateend date
Electricity
Council tax
Car insurance
Car road tax
Internet bill
Beer exam
Bacon ration

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]Nov 1, 2015[/TD]
[TD="align: right"]Dec 1, 2015[/TD]
[TD="align: right"]Jan 1, 2016[/TD]
[TD="align: right"]Feb 1, 2016[/TD]
[TD="align: right"]Jan 1, 2017[/TD]
[TD="align: right"]Jul 1, 2019[/TD]
[TD="align: right"]Jul 1, 2025[/TD]
[TD="align: right"]Jul 1, 2026[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$42.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Nov 4, 2013[/TD]
[TD="align: right"]Jul 7, 2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$103.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]May 8, 2014[/TD]
[TD="align: right"]Jul 12, 2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$500.00[/TD]
[TD="align: right"]Jan 3, 2016[/TD]
[TD="align: right"]Jan 2, 2017[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]500[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]500[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$180.00[/TD]
[TD="align: right"]Jun 1, 2015[/TD]
[TD="align: right"]May 31, 2016[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$10.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Sep 1, 2015[/TD]
[TD="align: right"]Mar 31, 2017[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]Jul 4, 2016[/TD]
[TD="align: right"]Jul 4, 2025[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]50[/TD]
[TD="bgcolor: #E2EFDA, align: right"]50[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$37.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Dec 17, 2015[/TD]
[TD="align: right"]Jun 30, 2026[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

</tbody>
Sheet24​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),MONTH($D2)=MONTH(F$1),YEAR($D2)<=YEAR(F$1),YEAR($E2)>=YEAR(F$1)),$C2,IF(AND(ISNUMBER($B2),$D2<=F$1,$E2>=F$1),$B2,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
DRSteele said:
It's not a "re-bump" --- the problem has not been solved, so post away until we're done. I've invented some more data to help us to get to the bottom of this.

I fixed my oversight in point 2; I stupidly thought it was a one-time payment. Now the formula will report a figure when a per annum payment month matches the month in the column and the year in the column is bookended by the payment start and end years. See below.

I don't quite follow what you mean in point 1; are you suggesting that my 37 payment on Dec 17/15 should show up in the Dec 1/15 column? Note that the dates in Row1 are all the first day of the month. Could you please clarify point 1?

ABCDEFGHIJKLM
per monthper annumstart dateend date
Electricity
Council tax
Car insurance
Car road tax
Internet bill
Beer exam
Bacon ration

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]Nov 1, 2015[/TD]
[TD="align: right"]Dec 1, 2015[/TD]
[TD="align: right"]Jan 1, 2016[/TD]
[TD="align: right"]Feb 1, 2016[/TD]
[TD="align: right"]Jan 1, 2017[/TD]
[TD="align: right"]Jul 1, 2019[/TD]
[TD="align: right"]Jul 1, 2025[/TD]
[TD="align: right"]Jul 1, 2026[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$42.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Nov 4, 2013[/TD]
[TD="align: right"]Jul 7, 2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA, align: right"]42[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$103.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]May 8, 2014[/TD]
[TD="align: right"]Jul 12, 2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA, align: right"]103[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$500.00[/TD]
[TD="align: right"]Jan 3, 2016[/TD]
[TD="align: right"]Jan 2, 2017[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]500[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]500[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$180.00[/TD]
[TD="align: right"]Jun 1, 2015[/TD]
[TD="align: right"]May 31, 2016[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$10.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Sep 1, 2015[/TD]
[TD="align: right"]Mar 31, 2017[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]Jul 4, 2016[/TD]
[TD="align: right"]Jul 4, 2025[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]50[/TD]
[TD="bgcolor: #E2EFDA, align: right"]50[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$37.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Dec 17, 2015[/TD]
[TD="align: right"]Jun 30, 2026[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]37[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

</tbody>
Sheet24​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER($C2),MONTH($D2)=MONTH(F$1),YEAR($D2)<=YEAR(F$1),YEAR($E2)>=YEAR(F$1)),$C2,IF(AND(ISNUMBER($B2),$D2<=F$1,$E2>=F$1),$B2,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thanks again DRSteele!

So for point 1, what i mean.... using your example in Row 8 (Bacon ration) above....
  • As you can see in your table, the first instance of Bacon Ration occurs in Dec 2015 (17th day). Therefore there should be an entry of $37 in cell G8. However, there is no entry, and the entries only begin from H8, I8, J8 onwards.
  • However, you will find, if you change the start date of Bacon Ration to Dec 01, 2015, suddenly $37 will appear in cell G8.
  • Dec 01, 2015 is the only start date that correctly enters $37 in cell G8. If you change the date to Dec 02, 2015; Dec 03, 2015, etc.... it will fail to show an entry in cell G8.

Hope it makes sense?

Indy
 
Upvote 0
I see I had mistakenly inferred that you did not want any payments that occurr mid-month to be included in that month's columns. I think finally knocked the sticky wicket:

Put this in F2 and copy AcrossAndDown

Code: 
=IF(AND(ISNUMBER($C2),MONTH($D2)=MONTH(F$1),YEAR($D2)<=YEAR(F$1),YEAR($E2)>=YEAR(F$1)),$C2,IF(AND(ISNUMBER($B2),(EOMONTH($D2,-1)+1)<=F$1,$E2>=F$1),$B2,""))

P.S. No need to reply with quotes...the post will become monstrous!
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Formula determine if a date falls between a specific M/D range for multiple years for YTD comparison.
Replies
6
Views
985
flashgordie
F
E
  • Question Question
Simple Formulas to list ALL Next payments due (Closest Date to Today) from a list
Replies
0
Views
653
EmadMassoud
E
MrDB4Excel
  • Question Question
Refund or Payment
Replies
2
Views
533
MrDB4Excel
MrDB4Excel
P
  • Solved
Calculate Year-to-date figures taken from another table in one cell based on selecting a month in a dropdown list
Replies
3
Views
631
Alex Blakenburg
A
D
  • Question Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
730
Herakles
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top