• 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...

  • 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

RIck151

New Member
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?
 

Attachments

RIck151

New Member
I would have to have a colander that updates, and that could only be done by opening the spreadsheet and updating the days date.
 

RIck151

New Member
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.
 

Thangavel

Active Member
In given example, col T shows the end of free time. then from that date you will charge .50 , till when you charge?
 

GraH - Guido

Well-Known Member
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.
 

Attachments

RIck151

New Member
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.
 

RIck151

New Member
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.
 

GraH - Guido

Well-Known Member
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.
 

Attachments

Last edited:

RIck151

New Member
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.
 
Top