# 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

• 26.4 KB Views: 23

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

#### Thangavel

##### Active Member
free time is based on date of received & not based on date of load?
Am I right?

#### 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?

#### RIck151

##### New Member
Great question. I would stop charging for holding the day before that date

#### RIck151

##### New Member
The loaded column. Calculations would stop the previous day of column k

#### RIck151

##### New Member
Pulling my hair out now trying to make something click

#### Thangavel

##### Active Member
We can get the total days you will charge. But i'm not sure to spilt them to last month.

#### RIck151

##### New Member
Fine... It's a start

#### 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

• 26.7 KB Views: 12

#### 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

• 28.2 KB Views: 14
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.