1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Monthly Storage Cost Calculation Formula

Discussion in 'Ask an Excel Question' started by RIck151, Dec 5, 2018.

  1. RIck151

    RIck151 New Member

    Messages:
    10
    Warehousing and Storage Cost Calculation Question

    I’m trying to figure out a formula that does this:

    I want 12 columns for each month. In these columns will be calculations that adds the cost of storage based on the number of days that particular item is being stored.

    The first 15 days of storage is free, then .50 afterwards. How can I have excel determine the amount each month the item is stored?

    You have the “Date Received”, “Date Loaded” (if loaded), “Free Time Ends Date”, and the 12 monthly columns.

    See the green columns

    Do I make since?

    Attached Files:

  2. RIck151

    RIck151 New Member

    Messages:
    10
    I would have to have a colander that updates, and that could only be done by opening the spreadsheet and updating the days date.
  3. Thangavel

    Thangavel Active Member

    Messages:
    103
    free time is based on date of received & not based on date of load?
    Am I right?
  4. RIck151

    RIck151 New Member

    Messages:
    10
    Correct The free 15 days start when it's received . So those 4 days in June would have been free, then 11 days in July would have been free. So the month of July would have had the 12th through the 31st cost calculated.
  5. Thangavel

    Thangavel Active Member

    Messages:
    103
    In given example, col T shows the end of free time. then from that date you will charge .50 , till when you charge?
  6. RIck151

    RIck151 New Member

    Messages:
    10
    Great question. I would stop charging for holding the day before that date
  7. Thangavel

    Thangavel Active Member

    Messages:
    103
    Which col you refering in the exel?
  8. RIck151

    RIck151 New Member

    Messages:
    10
    The loaded column. Calculations would stop the previous day of column k
  9. RIck151

    RIck151 New Member

    Messages:
    10
    Pulling my hair out now trying to make something click
  10. Thangavel

    Thangavel Active Member

    Messages:
    103
    We can get the total days you will charge. But i'm not sure to spilt them to last month.
  11. RIck151

    RIck151 New Member

    Messages:
    10
    Fine... It's a start
  12. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    852
    Not sure I completely understood the query, but you might try:

    1. add an empty column "Y"
    2. put real dates end of month in the month columns, but format as "MMMM" to show month names
    3. in [Z2] = MAX(0,IF(EOMONTH($T2,0)=Z$1,Z$1-$T2,MIN(DAY(Z$1),($T2<=Z$1)*$U2-SUM($Y2:Y2))))
    4. drag across and down

    EDIT: add *0.50 to the previous formula to have the amount to charge, not the number of days.

    Attached Files:

  13. RIck151

    RIck151 New Member

    Messages:
    10
    I'm currently doing some business traveling, so I'm going to give it a try as soon as I get to the office. Thank you guys so much for seeing what you can do.
  14. RIck151

    RIck151 New Member

    Messages:
    10
    Just trying things out...If I made date received 11/1, Date loaded 11/16, with free time ending 11/15; the # of storage days to charge (U2) is 1. The month of November (AJ2) shows 15 (7.5), but I would like for it to show 1 (.50). That's 15 free days, and 1 day that would be charged a storage fee.
  15. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    852
    Tweak like this?
    =MAX(0,IF(AND($U2>DAY(Z$1),EOMONTH($T2,0)=Z$1),Z$1-$T2,MIN(DAY(Z$1),($T2<=Z$1)*$U2-SUM($Y2:Y2))))

    *When I previously said *0.50 to have the amount, I meant it like this
    =MAX(0,IF(AND($U2>DAY(Z$1),EOMONTH($T2,0)=Z$1),Z$1-$T2,MIN(DAY(Z$1),($T2<=Z$1)*$U2-(SUM($Y2:Y2)*2))))*0,5

    This won't work if you have to change your price.

    Attached Files:

    Last edited: Dec 10, 2018 at 7:18 AM
  16. RIck151

    RIck151 New Member

    Messages:
    10
    I definitely would like to recycle the formula if I can get it going. We products at different prices How would I change the price for other uses? Oh yeah... Thank you again. This is really helping. I thought something like this wasn't even possible.

Share This Page