Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calc Prorated Cost based Specific Days in a Gven Month & Year Range.
Hello Everyone,
I beg your assistance. I'm struggling with this a little bit, and I think the formula required for this exceeds my knowledge. 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. We also need to do the calculation on a daily level as to make sure that all the days are accounted for. Let me give an example. Annual Cost: $1200.00 Days Service was used: 11/02/2004 - 03/16/06 Calculation: Monthly Price = $1200.00/12 Months = $100 Now, to calculate the daily price, I need to know how many days are in November of '04, December of '04, January of '05, February of '05, and so on. November '04 = 30 days. So the price per Day in November of 2004 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 Then the other months are easy enough, because they're full months. Then I'd need to do the same calculation on the back end, showing total days used in March '06. Adding this total cost per month/partial month, I can hopefully come up with a total amount due. I'm also trying to make sure that if I have two dates within the same month, I can calculate cost used. The variables are going to be the cost of the service, and the Start Date and End Date. I just need a total, not an actual cost per month, or per year. I can get a cost for a specific period by adjusting the start and end dates for that period. 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 want this to account for leap years, and since the need it there to implement it for the month of February, I figured that the rest of the months could have that manual calc as well. But I'm not the expert. :) I would appreciate any guidance, or help that anyone can provide me. Please and Thank You :), -John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calc Prorated Cost based Specific Days in a Gven Month & Year Range.
annual cost = 1200
daily cost = 1200 / 365 enter annual cost in cell B2 enter the two dates say in cells C2 and D2 formula in cell E2 .......=(D2-C2)*B2/365 result $1640.55 The "D2-C2" part of the formula will calculate the number of days for the whole period Greetings from New Zealand Bill K "RetailMessiah" wrote in message ups.com... Hello Everyone, I beg your assistance. I'm struggling with this a little bit, and I think the formula required for this exceeds my knowledge. 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. We also need to do the calculation on a daily level as to make sure that all the days are accounted for. Let me give an example. Annual Cost: $1200.00 Days Service was used: 11/02/2004 - 03/16/06 Calculation: Monthly Price = $1200.00/12 Months = $100 Now, to calculate the daily price, I need to know how many days are in November of '04, December of '04, January of '05, February of '05, and so on. November '04 = 30 days. So the price per Day in November of 2004 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 Then the other months are easy enough, because they're full months. Then I'd need to do the same calculation on the back end, showing total days used in March '06. Adding this total cost per month/partial month, I can hopefully come up with a total amount due. I'm also trying to make sure that if I have two dates within the same month, I can calculate cost used. The variables are going to be the cost of the service, and the Start Date and End Date. I just need a total, not an actual cost per month, or per year. I can get a cost for a specific period by adjusting the start and end dates for that period. 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 want this to account for leap years, and since the need it there to implement it for the month of February, I figured that the rest of the months could have that manual calc as well. But I'm not the expert. :) I would appreciate any guidance, or help that anyone can provide me. Please and Thank You :), -John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calc Prorated Cost based Specific Days in a Gven Month & Year Range.
RetailMessiah wrote: I would appreciate any guidance, or help that anyone can provide me. Bill's answer is right on. You might spend some time with date formulas. A brief (and good) intro can be found he http://www.meadinkent.co.uk/datesandtimes.htm Best of luck and hello from Olympia, Wa Dick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calc Prorated Cost based Specific Days in a Given Month & Year Range.
I've used that, although it would not be accurate during a leap year.
I'm looking for a permanent formula that can be used ongoing, and may be incorperated into a more complex sheet, or calc. That's why I was looking for monthly calculations. I will also be researching with the latter post in thread, and trying to determine if the month-by-month calc is even possible. Also, I'm not sure how to calculate the daily rate for a partial month. If they type in a date of 01/15/06, I'd want the formula to be able to calculate days in that month, and the following month, etc. Thanks for the help, and wish me luck reading up on this. :) Thanks, -John Bill Kuunders wrote: annual cost = 1200 daily cost = 1200 / 365 enter annual cost in cell B2 enter the two dates say in cells C2 and D2 formula in cell E2 .......=(D2-C2)*B2/365 result $1640.55 The "D2-C2" part of the formula will calculate the number of days for the whole period Greetings from New Zealand Bill K "RetailMessiah" wrote in message ups.com... Hello Everyone, I beg your assistance. I'm struggling with this a little bit, and I think the formula required for this exceeds my knowledge. 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. We also need to do the calculation on a daily level as to make sure that all the days are accounted for. Let me give an example. Annual Cost: $1200.00 Days Service was used: 11/02/2004 - 03/16/06 Calculation: Monthly Price = $1200.00/12 Months = $100 Now, to calculate the daily price, I need to know how many days are in November of '04, December of '04, January of '05, February of '05, and so on. November '04 = 30 days. So the price per Day in November of 2004 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 Then the other months are easy enough, because they're full months. Then I'd need to do the same calculation on the back end, showing total days used in March '06. Adding this total cost per month/partial month, I can hopefully come up with a total amount due. I'm also trying to make sure that if I have two dates within the same month, I can calculate cost used. The variables are going to be the cost of the service, and the Start Date and End Date. I just need a total, not an actual cost per month, or per year. I can get a cost for a specific period by adjusting the start and end dates for that period. 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 want this to account for leap years, and since the need it there to implement it for the month of February, I figured that the rest of the months could have that manual calc as well. But I'm not the expert. :) I would appreciate any guidance, or help that anyone can provide me. Please and Thank You :), -John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calc Date Based on Year | Excel Discussion (Misc queries) | |||
Can't get NETWORKDAYS to calc elapsed banking days in a month | Excel Worksheet Functions | |||
Calc days between two dates and exclude leap year days | Excel Worksheet Functions | |||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..) | Excel Discussion (Misc queries) | |||
I'm looking for a formula to calc # of days left in a month | Excel Worksheet Functions |