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

1. ## 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

2. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

the only way I can think is to add 24 more columns representing each of the months and putting a formula as such...
Attachment 425787

Was seeing if there was a way to do this that only showed the months in questions and not have 22-23 columns with zeros

3. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

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

4. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

Yaaa how do you get to that part directly like that as you did, I can only get there with a lot more steps

5. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

The formulas are shown.

6. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

I updated the Workbook to reflect how I get there, was wondering if there is a way to do this in a more efficient manner, not displaying the rows with zeros potentially, or in less steps? I tried the formula you gave me but for some reason it is working but doesn't pro-rate it properly for some rows, this could be a syntax issue I am not sure, you can look at the new Workbook I uploaded to see what I did....

7. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

See attached.

8. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

You sir are a scholar! Thank you kindly

9. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

You're welcome.

10. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

Originally Posted by shg
You're welcome.
Hey shg, there were errors in the formula, I explained it in the description at the end and edited my post with the attachment. Can you please take a look?

11. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

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?

12. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

Not sure if this solved it, I am still getting the broken up portion larger then the Gross amount which is not supposed to happen! If this formula is right, the different split up portions based on the formula that gets inputed into the different months should add up exactly to the gross amount each time, not more or less. Are you able to upload the workbook like you did last time? or perhaps investigate this another way. Thank you btw

13. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

Originally Posted by shg
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?
Not sure if this solved it, I am still getting the broken up portion larger then the Gross amount which is not supposed to happen! If this formula is right, the different split up portions based on the formula that gets inputed into the different months should add up exactly to the gross amount each time, not more or less. Are you able to upload the workbook like you did last time? or perhaps investigate this another way. Thank you btw

14. ## Re: Don't know how to proceed, can't prorate Gross Amount by different Months in Period gi

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?

There are currently 1 users browsing this thread. (0 members and 1 guests)