Formula for prorate based on calendar days/months?

[chino3]

Hello!

Need a little help... ermmm a lot of help!

I am modifying a spread sheet to determine a monthly prorate, but would have to pull based on calendar days for the respective month.

So for example;

I have base amount $1000, and want to figure out a the prorate for the remaining portion of the month. Say responsibility starts 7/16/13, the prorate through 7/31/13 would be $516.

Now this is where it gets a little tricky, if it is a month that only has 30 days, like 6/16/13 through 6/30/13 the total is $500.

I need the formula to determine the prorated days based on the month and listed dates (1,3,5,7,8,10,12 for 31 day prorates), (4,6,9,11 for 30 day prorates) and (2 for 28 day prorate).

I just cannot wrap my head around writing this.

Thanks for any help!

 

[JoeMo]

Excel Workbook
	A	B	C
1	Amount	StartDate	ProRateAmt
2	1000	6/16/2013	500.00
Sheet10

 

[chino3]

you are my hero! now to just understand it lol. thanks a ton!

 

[JoeMo]

you are my hero! now to just understand it lol. thanks a ton!

You are welcome. Thanks for your reply.

 

[chino3]

You are welcome. Thanks for your reply.

sorry, one quick question. Everything works great in excel 2007, but in 2003 its coming up with a *NAME error message. Any ideas?

 

[chino3]

sorry, one quick question. Everything works great in excel 2007, but in 2003 its coming up with a *NAME error message. Any ideas?

and one last question, and I will leave everyone alone!

How would I do the inverse? so if the financial responsibility ENDS on 7/16/13, calculating 7/1-7/16?

Thanks again!

 

[JoeMo]

It's been a long while since I used Excel 2003 so I'm not certain, but it may be that the EOMONTH function requires installing the Analysis Tool Pak (see Excel Help). For your inverse question: do you want the pro-rata assuming the start was the first of the month of the end date and up to the current date?

 

[chino3]

It's been a long while since I used Excel 2003 so I'm not certain, but it may be that the EOMONTH function requires installing the Analysis Tool Pak (see Excel Help). For your inverse question: do you want the pro-rata assuming the start was the first of the month of the end date and up to the current date?

I will look into the tool pack!

and for your question, it will always assume the 1st of the month will be the "starting" point. Always being 1/1-1/xx, 2/1-2/xx, etc.

 

[JoeMo]

I will look into the tool pack!

and for your question, it will always assume the 1st of the month will be the "starting" point. Always being 1/1-1/xx, 2/1-2/xx, etc.

Assuming you replace the start date in B2 with end date in B2 then the formula would be the complement of the one I posted earlier or:

=A2*(1-(DAY(EOMONTH(B2,0))-DAY(B2)+1)/DAY(EOMONTH(B2,0)))

 

[chino3]

hmmmm, maybe Im not being clear, or its just not calculating right (probably the former!).

so for instance I am looking the financial responsibility ending on 7/17/13. So it would be 7/1/13-7/17/13. Assuming the amount to prorate from is $1000 the amount owed would be $548

Sorry to make this difficult!