Need complex excel formula to plot revenue into each month given start and end dates

[DVM]

This may be an accounting function, but I am trying to do the following.

Let's say:
Contract is for $100,000
Start Date is 6/15/09
End Date is 9/30/09

I know I could subtract the dates and divide against $100,000 to get the daily revenue. I could then multiply the daily by the number of days in each month.

However, what I really want to do is get an Excel formula to calculate the monthly amount in the correct fields no matter what the start and end dates and no matter how many days in each month.

The answer in this case would be
Jun = $14,019
Jul = $28,972
Aug = $28.972
Sep = $28,037

Sept only has 30 days, whereas Jul and Aug have 31.

Thoughts?

 

[Byrooni]

My reporting has to be by month unfortunately...

Is anybody able to help me with my previous request - PLEASE!

This was my previous post:

Hi - this formula really helps me but there is a slight problem when the money falls on the same day i.e. if the start date and end date are the same day it returns a zero and then I get a #DIV/0!
Is there any way you can adjust the formula to cater for a scenario where the money is recognized in one full day?

I'm referring to the first post in this thread using the following formula:

=(IF(AND($C2>=D$1,$B2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),MIN($C2,DATE(YEAR(D$1),MONTH(D$1)+1,1))-MAX($B2,D$1),0)/($C2-$B2))*$A2

Desperately need your help on this...thanks.

 

[stattohogg]

assuming your data is in A1:P10

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

Amount​

Start​

End​

Jan-09​

Feb-09​

Mar-09​

Apr-09​

May-09​

Jun-09​

Jul-09​

Aug-09​

Sep-09​

Oct-09​

Nov-09​

Dec-09​

2​

940068​

26/12/2009​

25/04/2010​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

47003​

3​

463138​

28/01/2009​

28/05/2009​

15438​

108066​

119644​

115785​

104206​

0​

0​

0​

0​

0​

0​

0​

4​

275349​

28/03/2009​

26/07/2009​

0​

0​

9178​

68837​

71132​

68837​

57364​

0​

0​

0​

0​

0​

5​

644101​

07/10/2009​

04/02/2010​

0​

0​

0​

0​

0​

0​

0​

0​

0​

134188​

161025​

166393​

6​

409121​

19/04/2009​

17/08/2009​

0​

0​

0​

40912​

105690​

102280​

105690​

54549​

0​

0​

0​

0​

7​

171716​

31/10/2009​

28/02/2010​

0​

0​

0​

0​

0​

0​

0​

0​

0​

1431​

42929​

44360​

<colgroup><col style="WIDTH: 42pt" width="56"><col style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3626" width="85"><col style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3669" width="86"><col style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3498" width="82"><col style="WIDTH: 42pt" span="12" width="56"></colgroup><tbody>
</tbody>

D2

=(IF(AND($C2>=D$1,$B2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),MIN($C2,DATE(YEAR(D$1),MONTH(D$1)+1,1))-MAX($B2,D$1),0)/($C2-$B2))*$A2

copied down and across

This formula is great, almost exactly what I need, except it doesn't work on inclusive dates, so mis-calculates the values by one days worth each time, can anyone help me fix this?

Thanks

 

[kristyliao]

would you please help.

The revenue terms in A1 (annual) or M1 (monthly) is show by each month.
But we have "revenue by Quarter" that will only shows quarter revenue in first month of each qtr.

eg: revenue in
Jan~Mar. will show in Jan.
Apr.~June will show in Apr.
July~Sept. =>Jully
Oct.~ Dec. ==>Oct.


Jan. Feb. Mar. ==> Jan. Feb. Mar.
30 50 70 150 0 0

how to set the formula in a excel of 50,000 orders? thank you!

Amount

Start

End

terms

Jan-15

Feb-15

Mar-15

Apr-15

May-15

Jun-15

Jul-15

Aug-15

Sep-15

Oct-15

Nov-15

Dec-15

94068

12/26/2015

4/25/2016

A1

463138

1/28/2015

5/28/2016

M1

255349

3/28/2015

7/26/2017

M1

644101

4/10/2015

4/202017

Q1

409121

8/19/2014

8/17/2018

Q1

171716

11/31/2015

2/25/2017

Q1

<colgroup><col><col><col span="2"><col span="12"></colgroup><tbody>
</tbody>