Spreading out Billable Amounts

[Eb0la11]

I have a spreadsheet setup as follows:

Column A is a contract Amount (Example $100)
Column B is a Date (Month-Year as Sep-17) that represents the contracts start date
Column C is a Date (Month-Year as Sep-17) that represents the contracts end date
Column D through infinity is months of the year (Month-Year as Sep-17) starting with 4 months ago in column D, meaning todays month is in column H and the months continue on into infinity

I'd like to setup a formula that places that $100 into the corresponding month based on these conditions:

• The first month we are to start the project we place 5% of the contract amount.
• The rest of the contract is distributed into as many months as the remaining duration of the contract evenly distributed. If it ends in December we would have a dollar amount in January as the last amount.

If the dates change, the contract amount is redistributed again based on the change in dates.

Seems simple enough but I am struggling with it.

 

[Eb0la11]

Anyone able to help me solve this problem?

I have a spreadsheet setup as follows:

Column A is a contract Amount (Example $100)
Column B is a Date (Month-Year as Sep-17) that represents the contracts start date
Column C is a Date (Month-Year as Sep-17) that represents the contracts end date
Column D through infinity is months of the year (Month-Year as Sep-17) starting with 4 months ago in column D, meaning todays month is in column H and the months continue on into infinity

I'd like to setup a formula that places that $100 into the corresponding month based on these conditions:

• The first month we are to start the project we place 5% of the contract amount.
• The rest of the contract is distributed into as many months as the remaining duration of the contract evenly distributed. If it ends in December we would have a dollar amount in January as the last amount.

If the dates change, the contract amount is redistributed again based on the change in dates.

Seems simple enough but I am struggling with it.

 

[SyedUsman]

Pls try this & let me know

=IFERROR(IF(D1>$C$2,"",(IF(($B$2-D1)>0,EOMONTH(B2,0)-B2+1,IF(EOMONTH(D1,0)=EOMONTH($C$2,0),$C$2-D1+1,DAYS(EOMONTH(D1,0),EOMONTH(D1,-1)+0))))/($C$2-$B$2+1)*$A$2),"")

 

[SyedUsman]

Try this , includes both your criteria

=IFERROR(IF((EOMONTH(D1,0)-EOMONTH($B$2,0))>=0,IF(EOMONTH(D1,0)=EOMONTH($B$2,0),$A$2*0.05,IF(D1>$C$2,"",(IF(($B$2-D1)>0,EOMONTH(B2,0)-B2+1,IF(EOMONTH(D1,0)=EOMONTH($C$2,0),$C$2-D1+1,DAYS(EOMONTH(D1,0),EOMONTH(D1,-1)+0))))/(DAYS($C$2,EOMONTH($B$2,0)))*$A$2*0.95)),""),"")

 

[Eb0la11]

Pls try this & let me know

=IFERROR(IF(D1>$C$2,"",(IF(($B$2-D1)>0,EOMONTH(B2,0)-B2+1,IF(EOMONTH(D1,0)=EOMONTH($C$2,0),$C$2-D1+1,DAYS(EOMONTH(D1,0),EOMONTH(D1,-1)+0))))/($C$2-$B$2+1)*$A$2),"")

Its close, think it needs a bit more tweaking. Its placing 50% of the contract amount in months prior to the start so I believe the first portion needs to be adjusted to a less than sign as such (this is based on an example with oct 1 start and dec 1 finish so basically 2 month duration):

=IFERROR(IF(D1<$C$2,""

I changed it to less than as if its a month before the start date we would display no billings.

But I think another section needs to be changed too because once I dragged that across the months it put nothing in any months. Should be 5% in first month then equal portions of 95% in all other months until end date. Do you mind helping me tweak it once more?

 

[Eb0la11]

Ok I just saw your second formula. I missed it earlier!

I plugged it in and it perfectly put the 5% in the appropriate month, but the 95% was not displayed as evenly dispersed in remaining months after. There was no value placed in those cells at all. May have a small error with the order of the if statements? The formula has gotten quite long, can you assist in tweaking it?

 

[Eb0la11]

There are two instances of cell B1 that are not anchored in use in the EOMONTH function. Could this be where it is breaking down?

 

[Marcelo Branco]

Maybe something like this

Assumes
First day of each month in E1, F1.....
Dates (numbers) as Start/End dates in columns B:C
To make things easier i created a new column D

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

1​

Amount​

Start​

End​

Months​

mai/17​

jun/17​

jul/17​

ago/17​

set/17​

out/17​

nov/17​

dez/17​

jan/18​

fev/18​

mar/18​

abr/18​

2​

100​

set/17​

set/17​

1​

5,00​

95,00​

3​

120​

set/17​

nov/17​

3​

6,00​

38,00​

38,00​

38,00​

4​

140​

ago/17​

fev/18​

7​

7,00​

19,00​

19,00​

19,00​

19,00​

19,00​

19,00​

19,00​

5​

280​

jun/17​

dez/17​

7​

14,00​

38,00​

38,00​

38,00​

38,00​

38,00​

38,00​

38,00​

6​

80​

mai/17​

mai/17​

1​

4,00​

76,00​

Formula in D2 copied down
=(YEAR($C2)-YEAR($B2))*12+MONTH($C2)-MONTH($B2)+1

Formula in E2 copied across and down
=IF($B2=E$1,0.05*$A2,IF(AND(E$1>$B2,EOMONTH($C2,0)+1>=E$1),$A2*0.95/$D2,""))

Remark: i'm using excel Brazilian version, so notice that months abbreviations in Portuguese are different from English, but very similar (mai x may, ago x aug etc)

Hope this helps

M.

 

[C with no eyes]

Insert a column so column D is blank for this formula to work. Paste this formula into cell E2 then you can drag it in both directions:

=IF(AND(EOMONTH($B2,0)=EOMONTH(E$1,0),EOMONTH($C2,0)=EOMONTH(E$1,0)),$A2,IF(EOMONTH($B2,0)=EOMONTH(E$1,0),5/100*$A2,IF(AND(D2 > 0,$C2 > =E$1),(95/100*$A2)/DATEDIF(DATE(YEAR($B2),MONTH($B2),1),$C2,"m"),0)))

 

[SyedUsman]

Ok I just saw your second formula. I missed it earlier!

I plugged it in and it perfectly put the 5% in the appropriate month, but the 95% was not displayed as evenly dispersed in remaining months after. There was no value placed in those cells at all. May have a small error with the order of the if statements? The formula has gotten quite long, can you assist in tweaking it?

OK OK i thought u want it spread by days in a month , if u want it evenly till the next month of last month.

=IF(EOMONTH($B$2,-1)+1=D1,$A$2*0.05,(D1>=$B$2)*(D1<=EOMONTH($C$2,1))/(MONTH($C$2)-MONTH($B$2)+1)*0.95*$A$2)

*Note the months at the top are dated as the 1st day of the month like 01/01/2017 , 02/01/2017 as so on