Monthly Storage Cost Calculation Formula

[RIck151]

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?

 

[RIck151]

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]

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

 

[RIck151]

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]

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

 

[RIck151]

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

 

[Thangavel]

the day before that date

Which col you refering in the exel?

 

[RIck151]

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

 

[RIck151]

Pulling my hair out now trying to make something click

 

[Thangavel]

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

 

[RIck151]

Fine... It's a start

 

[GraH - Guido]

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.

 

[RIck151]

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]

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]

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.

 

[RIck151]

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.