Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)

[retailmessiah]

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

 

[Ron de Bruin]

Hi John

No time to look at your code at the moment but for EOMONTH

See
http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

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

 

[chip.gorman]

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, here:http://groups.google.com/group/microsoft.public.excel/browse_thread/t...

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

One spec was that the John's solution NOT use EOMONTH since the
Analysis Tookpak is unlikely to be turned on,.

I sent John a solution that uses a helper lookup table with the first
day of each month, the last day of each month, and the number of days
in the month. It then looks up the start and end dates and calculates
the appropriate prorations

Startmonth proration = (last day of start month - start date)+1 ,
Endmonth proation = (end date - first day of end month)+1

Add together the % proration for first month and last month, plus the
number of full months, and multiple by the monthly rate.

 

[chip.gorman]

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, here:http://groups.google.com/group/microsoft.public.excel/browse_thread/t...

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

Alternatively to my previous post (and what I emailed to John), the
following rather unwieldy formula would accomplish the same thing,
calculating the end of month the hard way each time:

=ROUND(H12*(((DATE(YEAR(Start_Date),MONTH(Start_Date)+1,1)-
DATE(YEAR(Start_Date),MONTH(Start_Date),1))-DAY(Start_Date)+1)/
(DATE(YEAR(Start_Date),MONTH(Start_Date)+1,1)-
DATE(YEAR(Start_Date),MONTH(Start_Date),1))+
(DAY(End_Date)/(DATE(YEAR(End_Date),MONTH(End_Date)+1,1)-
DATE(YEAR(End_Date),MONTH(End_Date),1)))+
(12-MONTH(Start_Date))+((YEAR(End_Date)-YEAR(Start_Date)-1)*12)+
(MONTH(End_Date)-1)),2)

H12 is the monthly rate.

Sub the start_date and end_date cells for each instance (or better,
name the ranges).

No helper ranges required on this one. I still prefer the lookup
version since it's easier to see what's happening.

 

[Ron Rosenfeld]

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

I'm not looking at your code, but if I understand you correctly, the algorithm
seems easy to implement in just worksheet code without requiring the ATP using
the following formulas and constants:

Start: Starting Date
End: Ending Date
Annual: $1200
Monthly: =Annual/12

First: Charge for First Month
=DAY(DATE(YEAR(Start),MONTH(Start)+1,0)-DAY(Start)
+1)*Monthly/DAY(DATE(YEAR(Start),MONTH(Start)+1,0))

Last: Charge for Last Month
=DAY(End)*Monthly/DAY(DATE(YEAR(End),MONTH(End)+1,0))

Rest: Charge for the Intervening months
=Monthly*DATEDIF(DATE(YEAR(Start),MONTH(Start)+1,1),
DATE(YEAR(End),MONTH(End),1),"m")

Total: =SUM(First,Last,Rest)


The number of days in any month is given by the formula:

=DAY(DATE(YEAR(dt),MONTH(dt)+1,0))

DATEDIF is present in Excel as a regular worksheet function, but is
undocumented except in Excel 2000. If you don't have that version, see
http://www.cpearson.com/excel/datedif.htm for documentation.

The above can be implemented in VBA Code if you wish:

==============================================
Option Explicit

Function SvcCost(StartDt, EndDt, AnnualRate) As Double
Dim First As Double
Dim Last As Double
Dim Rest As Double
Dim DaysInMonth As Long
Dim DailyCharge As Double
Dim NumOfMonths As Long

'compute first month charge
DaysInMonth = Day(DateSerial(Year(StartDt), Month(StartDt) + 1, 0))
DailyCharge = AnnualRate / 12 / DaysInMonth
First = DailyCharge * (DaysInMonth - Day(StartDt) + 1)

'compute last month charge
DaysInMonth = Day(DateSerial(Year(EndDt), Month(EndDt) + 1, 0))
DailyCharge = AnnualRate / 12 / DaysInMonth
Last = DailyCharge * (Day(EndDt))

'compute Rest of month charge
NumOfMonths = DateDiff("m", DateSerial(Year(StartDt), Month(StartDt) + 1, 1), _
DateSerial(Year(EndDt), Month(EndDt), 1))
Rest = NumOfMonths * AnnualRate / 12

SvcCost = First + Last + Rest

End Function
=========================================


--ron

 

[retailmessiah]

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
thisproratedamounts, 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/microsoft.public.excel/browse_thread/t...

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
calculateCosttotals 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 annualcost, which will change. I may
implement it using monthlycost, but all the numbers play off of each
other.
AnnualCost: $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 Dailyproratedbill 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

I'm not looking at your code, but if I understand you correctly, the algorithm
seems easy to implement in just worksheet code without requiring the ATP using
the following formulas and constants:

Start: Starting Date
End: Ending Date
Annual: $1200
Monthly: =Annual/12

First: Charge for First Month
=DAY(DATE(YEAR(Start),MONTH(Start)+1,0)-DAY(Start)
+1)*Monthly/DAY(DATE(YEAR(Start),MONTH(Start)+1,0))

Last: Charge for Last Month
=DAY(End)*Monthly/DAY(DATE(YEAR(End),MONTH(End)+1,0))

Rest: Charge for the Intervening months
=Monthly*DATEDIF(DATE(YEAR(Start),MONTH(Start)+1,1),
DATE(YEAR(End),MONTH(End),1),"m")

Total: =SUM(First,Last,Rest)

The number of days in any month is given by the formula:

=DAY(DATE(YEAR(dt),MONTH(dt)+1,0))

DATEDIF is present in Excel as a regular worksheet function, but is
undocumented except in Excel 2000. If you don't have that version, seehttp://www.cpearson.com/excel/datedif.htm for documentation.

The above can be implemented in VBA Code if you wish:

==============================================
Option Explicit

Function SvcCost(StartDt, EndDt, AnnualRate) As Double
Dim First As Double
Dim Last As Double
Dim Rest As Double
Dim DaysInMonth As Long
Dim DailyCharge As Double
Dim NumOfMonths As Long

'compute first month charge
DaysInMonth = Day(DateSerial(Year(StartDt), Month(StartDt) + 1, 0))
DailyCharge = AnnualRate / 12 / DaysInMonth
First = DailyCharge * (DaysInMonth - Day(StartDt) + 1)

'compute last month charge
DaysInMonth = Day(DateSerial(Year(EndDt), Month(EndDt) + 1, 0))
DailyCharge = AnnualRate / 12 / DaysInMonth
Last = DailyCharge * (Day(EndDt))

'compute Rest of month charge
NumOfMonths = DateDiff("m", DateSerial(Year(StartDt), Month(StartDt) + 1, 1), _
DateSerial(Year(EndDt), Month(EndDt), 1))
Rest = NumOfMonths * AnnualRate / 12

SvcCost = First + Last + Rest

End Function
=========================================

--ron- Hide quoted text -

- Show quoted text -

First, Chip and Ron de Bruin, thanks for your answers. You definetly
got me thinking about this one, and pointed me in the right direction.
Chip, I played with your suggestion over the weekend, but I was unable
to get it working for whatever reason. Also, it was the WEEKEND, so I
tried not to spend a whole lot of time on it.

Next, to Ron R: Thanks!, it looks like that's almost got it. If I
enter in dates that are 1 or more months apart, it seems to be giving
me the correct pricing. The issue, as far as I can tell is the
DATEDIF(Start Date, End Date, "m") errors out if in the same month. In
my sample sheet, I had 01/01/07 to 01/15/07, which cannot be expressed
in months (apparently.) Can you suggest a correct/appropriate IF
statement in that same cell to check this, and work around it? It
looks like it actually works all the rest of the time however, which
is amazing.

Here's the formula I have now, all in one of the results cells: [Watch
out, spammy!]
=(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))+(DAY($H$7)*H12/DAY(DATE(YEAR($H
$7),MONTH($H$7)+1,0)))+(H12*DATEDIF(DATE(YEAR($B$7),MONTH($B
$7)+1,1),DATE(YEAR($H$7),MONTH($H$7),1),"m"))

I could fork that with an IF, and have it just do the first calc,
[(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))] if it's in the same month, and
if not, then do the rest. It also seems to work correctly with
January, then February, which I thought might also cause a problem..
but looks like it's ok for whatever reason.

So I imagine the logic test function isn't SAMEMONTH($B$7, $H$7).. Any
Ideas?

Thanks again to everyone involved with this thread. I'm so excited
this may finally work!

-John, retailmessiah (e-mail address removed)

 

[chip.gorman]

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
thisproratedamounts, 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/microsoft.public.excel/browse_thread/t...
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)
On Mar 8, 9:31 pm, (e-mail address removed) 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
calculateCosttotals 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 annualcost, which will change. I may
implement it using monthlycost, but all the numbers play off of each
other.
AnnualCost: $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 Dailyproratedbill 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

I'm not looking at your code, but if I understand you correctly, the algorithm
seems easy to implement in just worksheet code without requiring the ATP using
the following formulas and constants:

Start: Starting Date
End: Ending Date
Annual: $1200
Monthly: =Annual/12

First: Charge for First Month
=DAY(DATE(YEAR(Start),MONTH(Start)+1,0)-DAY(Start)
+1)*Monthly/DAY(DATE(YEAR(Start),MONTH(Start)+1,0))

Last: Charge for Last Month
=DAY(End)*Monthly/DAY(DATE(YEAR(End),MONTH(End)+1,0))

Rest: Charge for the Intervening months
=Monthly*DATEDIF(DATE(YEAR(Start),MONTH(Start)+1,1),
DATE(YEAR(End),MONTH(End),1),"m")

Total: =SUM(First,Last,Rest)

The number of days in any month is given by the formula:

DATEDIF is present in Excel as a regular worksheet function, but is
undocumented except in Excel 2000. If you don't have that version, seehttp://www.cpearson.com/excel/datedif.htmfor documentation.

The above can be implemented in VBA Code if you wish:

==============================================
Option Explicit

Function SvcCost(StartDt, EndDt, AnnualRate) As Double
Dim First As Double
Dim Last As Double
Dim Rest As Double
Dim DaysInMonth As Long
Dim DailyCharge As Double
Dim NumOfMonths As Long

'compute first month charge
DaysInMonth = Day(DateSerial(Year(StartDt), Month(StartDt) + 1, 0))
DailyCharge = AnnualRate / 12 / DaysInMonth
First = DailyCharge * (DaysInMonth - Day(StartDt) + 1)

'compute last month charge
DaysInMonth = Day(DateSerial(Year(EndDt), Month(EndDt) + 1, 0))
DailyCharge = AnnualRate / 12 / DaysInMonth
Last = DailyCharge * (Day(EndDt))

'compute Rest of month charge
NumOfMonths = DateDiff("m", DateSerial(Year(StartDt), Month(StartDt) + 1, 1), _
DateSerial(Year(EndDt), Month(EndDt), 1))
Rest = NumOfMonths * AnnualRate / 12

SvcCost = First + Last + Rest

End Function
=========================================

--ron- Hide quoted text -

- Show quoted text -

First, Chip and Ron de Bruin, thanks for your answers. You definetly
got me thinking about this one, and pointed me in the right direction.
Chip, I played with your suggestion over the weekend, but I was unable
to get it working for whatever reason. Also, it was the WEEKEND, so I
tried not to spend a whole lot of time on it.

Next, to Ron R: Thanks!, it looks like that's almost got it. If I
enter in dates that are 1 or more months apart, it seems to be giving
me the correct pricing. The issue, as far as I can tell is the
DATEDIF(Start Date, End Date, "m") errors out if in the same month. In
my sample sheet, I had 01/01/07 to 01/15/07, which cannot be expressed
in months (apparently.) Can you suggest a correct/appropriate IF
statement in that same cell to check this, and work around it? It
looks like it actually works all the rest of the time however, which
is amazing.

Here's the formula I have now, all in one of the results cells: [Watch
out, spammy!]
=(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))+(DAY($H$7)*H12/DAY(DATE(YEAR($H
$7),MONTH($H$7)+1,0)))+(H12*DATEDIF(DATE(YEAR($B$7),MONTH($B
$7)+1,1),DATE(YEAR($H$7),MONTH($H$7),1),"m"))

I could fork that with an IF, and have it just do the first calc,
[(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))] if it's in the same month, and
if not, then do the rest. It also seems to work correctly with
January, then February, which I thought might also cause a problem..
but looks like it's ok for whatever reason.

So I imagine the logic test function isn't SAMEMONTH($B$7, $H$7).. Any
Ideas?

Thanks again to everyone involved with this thread. I'm so excited
this may finally work!

-John, retailmessiah (e-mail address removed)

John, did you get the two spreadsheets I emailed to you?

The logic for the month test would be MONTH(B7)=MONTH(H7), but you
should not need it in the two versions I emailed to you. Each handled
same-month start and ends OK.

 

[Ron Rosenfeld]

First, Chip and Ron de Bruin, thanks for your answers. You definetly
got me thinking about this one, and pointed me in the right direction.
Chip, I played with your suggestion over the weekend, but I was unable
to get it working for whatever reason. Also, it was the WEEKEND, so I
tried not to spend a whole lot of time on it.

Next, to Ron R: Thanks!, it looks like that's almost got it. If I
enter in dates that are 1 or more months apart, it seems to be giving
me the correct pricing. The issue, as far as I can tell is the
DATEDIF(Start Date, End Date, "m") errors out if in the same month. In
my sample sheet, I had 01/01/07 to 01/15/07, which cannot be expressed
in months (apparently.) Can you suggest a correct/appropriate IF
statement in that same cell to check this, and work around it? It
looks like it actually works all the rest of the time however, which
is amazing.

Here's the formula I have now, all in one of the results cells: [Watch
out, spammy!]
=(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))+(DAY($H$7)*H12/DAY(DATE(YEAR($H
$7),MONTH($H$7)+1,0)))+(H12*DATEDIF(DATE(YEAR($B$7),MONTH($B
$7)+1,1),DATE(YEAR($H$7),MONTH($H$7),1),"m"))

I could fork that with an IF, and have it just do the first calc,
[(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))] if it's in the same month, and
if not, then do the rest. It also seems to work correctly with
January, then February, which I thought might also cause a problem..
but looks like it's ok for whatever reason.

So I imagine the logic test function isn't SAMEMONTH($B$7, $H$7).. Any
Ideas?

Thanks again to everyone involved with this thread. I'm so excited
this may finally work!

My suggestion would be to use, instead, the UDF I posted, as that will work
even if the months are the same.


--ron

 

[Ron Rosenfeld]

First, Chip and Ron de Bruin, thanks for your answers. You definetly
got me thinking about this one, and pointed me in the right direction.
Chip, I played with your suggestion over the weekend, but I was unable
to get it working for whatever reason. Also, it was the WEEKEND, so I
tried not to spend a whole lot of time on it.

Next, to Ron R: Thanks!, it looks like that's almost got it. If I
enter in dates that are 1 or more months apart, it seems to be giving
me the correct pricing. The issue, as far as I can tell is the
DATEDIF(Start Date, End Date, "m") errors out if in the same month. In
my sample sheet, I had 01/01/07 to 01/15/07, which cannot be expressed
in months (apparently.) Can you suggest a correct/appropriate IF
statement in that same cell to check this, and work around it? It
looks like it actually works all the rest of the time however, which
is amazing.

Here's the formula I have now, all in one of the results cells: [Watch
out, spammy!]
=(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))+(DAY($H$7)*H12/DAY(DATE(YEAR($H
$7),MONTH($H$7)+1,0)))+(H12*DATEDIF(DATE(YEAR($B$7),MONTH($B
$7)+1,1),DATE(YEAR($H$7),MONTH($H$7),1),"m"))

I could fork that with an IF, and have it just do the first calc,
[(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))] if it's in the same month, and
if not, then do the rest. It also seems to work correctly with
January, then February, which I thought might also cause a problem..
but looks like it's ok for whatever reason.

So I imagine the logic test function isn't SAMEMONTH($B$7, $H$7).. Any
Ideas?

Thanks again to everyone involved with this thread. I'm so excited
this may finally work!

-John, retailmessiah (e-mail address removed)

As I wrote, my first choice would be to use the UDF. It seems simpler to me.

If you absolutely must use a formula solution, you can change the formula for
REST:

=IF(ISERR(DATEDIF(DATE(YEAR(Start),MONTH(Start)+1,1),
DATE(YEAR(End),MONTH(End),1),"m")),-Monthly,Monthly*
DATEDIF(DATE(YEAR(Start),MONTH(Start)+1,1),DATE(YEAR(End),MONTH(End),1),"m"))

The problem is that the DATEDIF worksheet formula will give an error is the
first argument is later than the second argument. So we test for the error,
and act accordingly.

This is not a problem with the VBA DATEDIFF function, as it will give a
negative result in that instance.
--ron