Tiered Pricing with a twist - montly billing for a long term tier system

[dustyjay16]

Hi Everyone, I have what has been a tricky problem for me. I have seen several posts on tiered pricing, which have been very helpful for calculating a basic tiered price system. But here's my situation: Items stored are on a tiered basis. If an item has been at a location for 0-15 days, its free. 16-40 days, its $10/day. And 41+ days is $60/day. Lots of items can be stored at a location at once, and customers are billed monthly on the amount of items they have stored. So for instance a customer may have items A, B, and C at the location. Item A had been there for 45 days before this month started, and left on the 15th of the month. So item A would cost the customer 15 days this month *$60/day = $900 this month. Item B had been there for 12 days before this month started, and stayed all month long. So Item B's first 3 days this month were free (making 15), next 25 days (total day 16-40) were $10/day, and the last 2 or 3 days of the month were $60/day (because the 41+ days were reached). Etc Etc. So can anyone please help me understand the best way this can be calculated in excel using the total days the item has been there, and also the total days this month it was there? If I have those two pieces of info, and the pricing tiers, how would I calculate the total costs for that month? THANK YOU THANK YOU THANK YOU for any help you can provide.

 

[Redwolfx]

Welcome to the Forum.

Provided I'm understanding everything correctly Try this
... both Formulas are looking in Cell E2 for a date You will need to adjust that cell reference to fit your sheet
For Two months ago

=IF(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2<=15,0,IF(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2<=40,(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2-15)*10,((DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2-40)*60)+(((DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2)-15-(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2-40))*10)))-IF(DATE(YEAR(TODAY()),MONTH(TODAY())-2,1)-E2<=15,0,IF(DATE(YEAR(TODAY()),MONTH(TODAY())-2,1)-E2<=40,(DATE(YEAR(TODAY()),MONTH(TODAY())-2,1)-E2-15)*10,((DATE(YEAR(TODAY()),MONTH(TODAY())-2,1)-E2-40)*60)+(((DATE(YEAR(TODAY()),MONTH(TODAY())-2,1)-E2)-15-(DATE(YEAR(TODAY()),MONTH(TODAY())-2,1)-E2-40))*10)))

For Last Month

=IF(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-E2<=15,0,IF(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-E2<=40,(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-E2-15)*10,((DATE(YEAR(TODAY()),MONTH(TODAY()),1)-E2-40)*60)+(((DATE(YEAR(TODAY()),MONTH(TODAY()),1)-E2)-15-(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-E2-40))*10)))-IF(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2<=15,0,IF(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2<=40,(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2-15)*10,((DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2-40)*60)+(((DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2)-15-(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)-E2-40))*10)))

 

[dustyjay16]

Hey everyone! I actually just had an epiphany, and realized since I knew the total days and had the formula to calculate the total cost since the item got there, and I had the days this month so I could calculate the total cost before this month started, I can just take the difference in the total cost at month end minus the total cost before the month started. I about jumped for joy when I realized I was just missing that. Thank you very much for your responses and help though. I appreciate it greatly!