R

#### retailmessiah

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, here:

http://groups.google.com/group/micr...k=st&q=retailmessiah&rnum=40#fbe22c99294b99cf

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 (e-mail address removed)

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