Calc Prorated Cost based on Specific Days in a Given Month & Year Range
- From: retailmessiah@xxxxxxxxx
- Date: 8 Mar 2007 18:31:02 -0800
Hello Everyone,
I beg your assistance.
I posted this last year, and reread this recently. I've come to
realize how crappily I described this, and thought I'd give it another
go. 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, but we prorate it to only the days used. We also need to do
the calculation on a daily level as the price for service is Monthly,
regardless of how many days are in a month. We do however calculate it
down to the day. Confused yet? Let me give an example.
This is all based on the annual cost, which will change. I may
implement it using monthly cost, but all the numbers play off of each
other.
Annual Cost: $1200.00 (which is Monthly Price = $1200.00/12 Months =
$100)
Days Service was used: 11/02/2004 - 03/16/06
Price per Day: [28 Day Month]= $3.57/day, [29 Day Month]=$3.45/day,
[30 Day Month]=$3.33/day, [31 Day Month]=$3.23/day
Ok, so they only have to pay for the days that they used.
Now, to calculate the daily price, I need to know how many days are in
November of '04, and March of '06. All the rest we can use static
monthly pricing. So, let's get back to basics:
Thirty days hath September, April, June, and November; All the rest
have thirty-one Excepting February alone: Which hath but twenty-eight,
in fine, Till Leap Year gives it twenty-nine.
November '04 = 30 days. So the price per Day for November 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
March 06 = 31 days. So the price per Day for March is $3.23/day.
Taking into account that the service ended on March 16th, that would
mean 16 days of service were used. Final March calc would be 16 *
$3.23 = $51.68
December '04, the 12 months of 2005, and January '06, and February '06
= 15 Months, which is $1500.
Total Daily prorated bill for this customer would be: $1648.25.
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'm pretty good with subtracting dates, but I don't know how to
tell excel to do that with the first and last. I'm struggling with how
to separate full months from partial ones. I also need this to account
for leap years. The leap year, .. well, the days in the month only
matter if the starting date or ending date fall in the middle of the
month.
I hope this better explains the issue, so that by asking the right
question, I can hope to receive the right answer. :) I would
appreciate any guidance, or help that anyone can provide me.
Please and Thank You :),
-John
.
- Follow-Ups:
- Prev by Date: Re: Stars instead of numbers
- Next by Date: Re: excel vba - open workbooks hidden?
- Previous by thread: excel vba - open workbooks hidden?
- Next by thread: Re: Calc Prorated Cost based on Specific Days in a Given Month & Year Range
- Index(es):
Relevant Pages
|
