Excel - Using invoices with dates and $'s and having them figure out monthly contribution? 1

[kidnos]

Hi all, I have an excel sheet that has a number of invoices that include dollar amount and the start date and completion date. So one is $200K and it goes from 9/1/2012 to 9/1/2013.
If I divide the amount by 12 then I figure out the monthly contribution. However, I don't know how to automate it and set it up with the dates. So lets say I have a columns with each month listed for 2012 and 2013 is there a way or formula to have the data go from September 1, 2012 to September 1, 2013? Also, would it be able to account for dates that aren't the beginning of the month (i.e. like the 15th?). I'm a little stumped.
As always, thank you for the help.

Mike

 

[SkipVought]

Hi,

First you must have REAL DATES for your data and the column headings for 9/1/2012 to 9/1/2013. If you enter the first date heading, then each adjacent headind would be a formula like this, assuming that cell is D1...
[tt]
=Date(Year(D1),Month(D1),1)
[/tt]
Copy & Paste across until you get to 10/1/2013: yes, one month past your last date.

What does your account data table look like. Please paste a representative sample including headings.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Oops, forgot the increment!!!
[tt]
=Date(Year(D1),Month(D1)+1,1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kidnos]

Thanks Skip. That worked for the dates. This is what the data looks like. I think I may need to calculate the number of days in the month in order to get the contribution for each month. The data below starts at A1 (Customer)

Customer Amount Effective Date Completion Date
OSS $199,185 9/20/2012 6/29/2013
OSS $935,119 10/17/2012 10/18/2013
OSS $849,466 11/16/2012 9/18/2013
OSS $491,640 12/13/2012 5/13/2013
OSS $789,517 9/24/2012 6/29/2013

I have the months starting in E1 (9/1/2012) and have copied and pasted the formula you provided (till 1/1/2014).
Thank you.

 

[SkipVought]

In E2 enter this formula
[tt]
E2: =IF((Effective_Date<=F$1)*(Completion_Date=>E$1),($B2),0)/COUNTIF($E2:$AH2,">"&0)
[/tt]
Set Excel Options > Formulas > and CHECK Enable Iterations.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kidnos]

Skip - I tried to conform to the way that monthly/quarterly results are reported and hard coded the dates. I used the formula provided but think I did something wrong.
So for the first amount ($199,185), it should start to contribute for part of September 2012 (first day of revenue is the 20th) and end in June 2013(last day of revenue is the 29th).
The total amount of days is 282 by my calculation which = 706.33/day. Then that value would be multiplied by the number of days applicable in that month.
I tried to work the second dollar amount ($935,119) but I can't seem to figure out the end part (when to stop it from contributing)
I uploaded the file on my skydrive.
Thank you,
Mike

 

[SkipVought]

[tt]
=IF((Effective_Date<=F$1)*(Completion_Date>=E$1),($B2),0)*IF(AND(Effective_Date<=E$1,Completion_Date>=F$1),F$1-E$1,IF(AND(Effective_Date>E$1,Completion_Date<F$1),Completion_Date-Effective_Date+1,IF(AND(Effective_Date>E$1,Effective_Date<F$1),F$1-Effective_Date,IF(AND(Completion_Date>E$1,Completion_Date<F$1),Completion_Date-E$1+1,0))))/(Completion_Date-Effective_Date+1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kidnos]

Perfect, you rock!

 

[kidnos]

Skip - just did a test run. Set the amount at $100. Effective date 1/1/2013 and completion date 2/1/2013. For some reason the formula is pulling in 94 and then 6.
I am pretty sure it should be 97 and 3 based on 31 days in January and 1 day in Feb.
Does this sound right to you?

 

[SkipVought]

Did you bother to play around with the limits of the formula?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}