Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)
Hi John
No time to look at your code at the moment but for EOMONTH See http://www.dicks-blog.com/archives/2...-addin-part-2/ -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)
On Sep 28, 10:22 am, wrote:
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, hehttp://groups.google.com/group/micro...rowse_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 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 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. |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)
On Sep 28, 10:22 am, wrote:
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, hehttp://groups.google.com/group/micro...rowse_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 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 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_Da te)+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. |
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)
On Sep 29, 4:57 am, Ron Rosenfeld wrote:
On Fri, 28 Sep 2007 08:22:23 -0700, wrote: 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, he http://groups.google.com/group/micro...rowse_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 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 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 |
#7
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)
On Oct 1, 1:30 pm, wrote:
On Sep 29, 4:57 am, Ron Rosenfeld wrote: On Fri, 28 Sep 2007 08:22:23 -0700, wrote: 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, he http://groups.google.com/group/micro...rowse_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 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 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.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 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. |
#8
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)
|
#9
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)
On Mon, 01 Oct 2007 11:30:36 -0700, wrote:
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 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(YE AR(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To detect month or days or year in if formula | Excel Discussion (Misc queries) | |||
Get Data based on Month and Year | Excel Worksheet Functions | |||
I'm looking for a formula to calc # of days left in a month | Excel Worksheet Functions | |||
Locate month n year from range of date | New Users to Excel | |||
count dates within range by year and month | Excel Worksheet Functions |