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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][td="bgcolor: #DCE6F1"]

M​

[/td][td="bgcolor: #DCE6F1"]

N​

[/td][td="bgcolor: #DCE6F1"]

O​

[/td][td="bgcolor: #DCE6F1"]

P​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Amount​

[/td][td]

Start​

[/td][td]

End​

[/td][td]

Months​

[/td][td]

mai/17​

[/td][td]

jun/17​

[/td][td]

jul/17​

[/td][td]

ago/17​

[/td][td]

set/17​

[/td][td]

out/17​

[/td][td]

nov/17​

[/td][td]

dez/17​

[/td][td]

jan/18​

[/td][td]

fev/18​

[/td][td]

mar/18​

[/td][td]

abr/18​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

100​

[/td][td]

set/17​

[/td][td]

set/17​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td]

5,00​

[/td][td]

95,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

120​

[/td][td]

set/17​

[/td][td]

nov/17​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td]

6,00​

[/td][td]

38,00​

[/td][td]

38,00​

[/td][td]

38,00​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

140​

[/td][td]

ago/17​

[/td][td]

fev/18​

[/td][td]

7​

[/td][td][/td][td][/td][td][/td][td]

7,00​

[/td][td]

19,00​

[/td][td]

19,00​

[/td][td]

19,00​

[/td][td]

19,00​

[/td][td]

19,00​

[/td][td]

19,00​

[/td][td]

19,00​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

280​

[/td][td]

jun/17​

[/td][td]

dez/17​

[/td][td]

7​

[/td][td][/td][td]

14,00​

[/td][td]

38,00​

[/td][td]

38,00​

[/td][td]

38,00​

[/td][td]

38,00​

[/td][td]

38,00​

[/td][td]

38,00​

[/td][td]

38,00​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

80​

[/td][td]

mai/17​

[/td][td]

mai/17​

[/td][td]

1​

[/td][td]

4,00​

[/td][td]

76,00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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