Calc Prorated Cost based Specific Days in a Gven Month & Year Range.

[RetailMessiah]

Hello Everyone,

I beg your assistance.

I'm struggling with this a little bit, and I think the formula required
for this exceeds my knowledge. I have the need to take 2 dates,
possibly in different years, and calculate Cost totals between them.
The tricky part is that the price is annual. We also need to do the
calculation on a daily level as to make sure that all the days are
accounted for. Let me give an example.

Annual Cost: $1200.00
Days Service was used: 11/02/2004 - 03/16/06
Calculation: Monthly Price = $1200.00/12 Months = $100

Now, to calculate the daily price, I need to know how many days are in
November of '04, December of '04, January of '05, February of '05, and
so on.

November '04 = 30 days. So the price per Day in November of 2004 is
$3.33/day. Taking into account that the service started on November
2nd, that would mean 29 days of service were used. Final November calc
would be 29 * $3.33 = $96.57

Then the other months are easy enough, because they're full months.
Then I'd need to do the same calculation on the back end, showing total
days used in March '06. Adding this total cost per month/partial month,
I can hopefully come up with a total amount due. I'm also trying to
make sure that if I have two dates within the same month, I can
calculate cost used. The variables are going to be the cost of the
service, and the Start Date and End Date. I just need a total, not an
actual cost per month, or per year. I can get a cost for a specific
period by adjusting the start and end dates for that period. All the
times I've attempted this, it seems to work sometimes, but usually not
in the same month. Other things that I'm concerned about is creating a
formula that will evaluate how many days are in the specific month and
year. I want this to account for leap years, and since the need it
there to implement it for the month of February, I figured that the
rest of the months could have that manual calc as well. But I'm not the
expert.

I would appreciate any guidance, or help that anyone can provide me.

Please and Thank You ,
-John

 

[Bill Kuunders]

annual cost = 1200
daily cost = 1200 / 365

enter annual cost in cell B2
enter the two dates say in cells C2 and D2

formula in cell E2 .......=(D2-C2)*B2/365
result $1640.55


The "D2-C2" part of the formula will calculate the number of days for the
whole period

Greetings from New Zealand
Bill K

 

[idim]

I would appreciate any guidance, or help that anyone can provide me.

Bill's answer is right on. You might spend some time with date
formulas. A brief (and good) intro can be found here:

http://www.meadinkent.co.uk/datesandtimes.htm

Best of luck and hello from Olympia, Wa

Dick

 

[RetailMessiah]

I've used that, although it would not be accurate during a leap year.
I'm looking for a permanent formula that can be used ongoing, and may
be incorperated into a more complex sheet, or calc. That's why I was
looking for monthly calculations. I will also be researching with the
latter post in thread, and trying to determine if the month-by-month
calc is even possible.

Also, I'm not sure how to calculate the daily rate for a partial month.
If they type in a date of 01/15/06, I'd want the formula to be able to
calculate days in that month, and the following month, etc.

Thanks for the help, and wish me luck reading up on this.

Thanks,
-John