Hello Excel Experts,

I write you again on this issue, sadly still unsolved. I've received
some replies, but none that are fully workable (or ones that I could
get working.) I've decided to give this another shot, as I've received
very useful information from this group before. I wanted to provide
some additional info as well, to hopefully get this one licked.

So, we have a calc that works, most of the time. We use it to compute
this prorated amounts, and usually it's not an issue. Some of the cons
(and the reason for the redo) is because it does not work across
multiple years, and does not take leap years into account. I'm looking
for a formula (hopefully an IF/OR/THEN/ELSE one, but I'll settle for
VBScript if needed) to get the NEW calculator that I'm working on do
this, with the base functionality of the old one. Also, the old one
(not written by me) seems to be a little rough around the edges, and
not working as well as it should/could? It spans multiple sheets, and
pulls information from a plethora of cells, that I feel like could
possibly be condensed.

So, to help with this, I've uploaded both of the calcs, for anyone to
review. This Google Groups doesn't allow file attachments, so I'll
have to give URLs. They do have macros, but you can disable them and
still have full functionality. Both calcs are setup to have monthly
amounts entered, and then compute it. I may add annual amount
conversion later, but for now, we're working with monthly pricing. So,
if all information from the scenario is entered into the working calc,
then it should yield the correct amount due. The non-working new calc
uses a formula based on 365-day pricing, and thus the numbers are
slightly off.

The working, current Calculator, that does not traverse multiple
years, or work with leap years is at:
http://www.pixeldev.net/john/ProratedCalc-Working.xls

The new design, non-working version is at:
http://www.pixeldev.net/john/NewProratedCalc.xls

If you need to contact me directly, or attach a working beta, please
do so at the gmail address. I look forward to working with this
community on this again, and hopefully finding resolution. Please also
see my post below as it contains all the details on my quest, as well
as a test scenario to illustrate what I'm trying to do. Another note:
One of the replies I did receive suggested a formula using EOMONTH.
While this may work, if our associates don't have the Analysis ToolPak
Add-in, it will only error out. I understand that there may be
workarounds for EOMONTH however. I've already inquired to IT, and
there is no possibility of pushing the toolpak to every workstation.

For additional details, for full disclosure, please see the entire
thread with replies, he
http://groups.google.com/group/micro...e22c99294b99cf

Lastly, I'd just like to thank everyone who participates in this
community. I know some do it because they enjoy it, and some use it as
a learning place. I use it as a resource, and am very grateful that
the subject matter experts here are kind, and helpful, anytime I need
them.

Thanks again,
-John, retailmessiah

On Mar 8, 9:31 pm, wrote:
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