Don't know how to proceed, can't prorate Gross Amount by different Months in Period given!

Hi all,

Within a report I receive it gives me a Billing period between 2 dates, sometimes these dates are at the end of one month and the beginning of another month. I must prepare a costing per month view. I have used logic in Excel to break up the “10/19/2014 - 11/1/2014 (Final Invoice)” string into a start and end date. With these dates I have identified how many days per month this range falls under, it could be all one month, it could be 2 months and I need to break up the Gross Ammount in its respective month based on the proportion.

Please check this file Attached: Sample Pro Rate.xlsx

shg can you check the fabulous formula you gave me, for some reason the formula breaks up the dates in the right spot but when you add the total amount it will be higher or lower than the total Gross Amount which it shouldn't be. I applied conditional formatting highlighting these cells in Yellow. Can you correct this problem? If you can you are wonderful

Like this?

Row\Col	D	E	F	G	H	I	J	K	L	M	N	O
1	Start	End	Wkdays	Amount	Sep 14	Oct 14	Nov 14	Dec 14	Jan 15	Feb 15
2	19 Oct 2014	01 Nov 2014	10	$ 2,560	$ 0	$ 2,560	$ 0	$ 0	$ 0	$ 0		F2: =NETWORKDAYS(D2, E2)
3	02 Nov 2014	15 Nov 2014	10	$ 3,200	$ 0	$ 0	$ 3,200	$ 0	$ 0	$ 0		H2: =MAX(0, NETWORKDAYS(MAX(H$1, $D2), MIN(EDATE(H$1, 1) - 1, $E2))) * $G2 / $F2
4	16 Nov 2014	29 Nov 2014	10	$ 2,560	$ 0	$ 0	$ 2,560	$ 0	$ 0	$ 0
5	30 Nov 2014	13 Dec 2014	10	$ 850	$ 0	$ 0	$ 0	$ 850	$ 0	$ 0
6	14 Dec 2014	27 Dec 2014	10	$ 2,080	$ 0	$ 0	$ 0	$ 2,080	$ 0	$ 0
7	28 Dec 2014	10 Jan 2015	10	$ 300,000	$ 0	$ 0	$ 0	$ 90,000	$ 210,000	$ 0
8	11 Jan 2015	24 Jan 2015	10	$ 2,424	$ 0	$ 0	$ 0	$ 0	$ 2,424	$ 0

I removed your holidays from your formulas - it was linked to another workbook.

The formulas are shown.

See attached.

You're welcome.

F2: =NETWORKDAYS(D2,E2,Holidays!$A$3:$C$11)

S2: =MAX(0,NETWORKDAYS(MAX(R$1,$D2),MIN(EDATE(R$1,1)-1,$E2),Holidays!L1399:N1407))*$AH2/$F2

Does that look right to you?

No, that won't solve it. I was trying to get you to see the problem.

What range contains the holidays?

Why are those ranges different in the two formulas?