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]

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!

That's different than what I thought you were asking for. Try:

=A2*(DAY(B2)/DAY(EOMONTH(B2,0)))

where B2 contains the date the responsibility ends.

 

[chino3]

perfecto! thank you good sir!

 

[Mandy_84]

what if cell B2 is 0 (no date at all), so I expect cell C2 to be blank?

 

[Mandy_84]

1