Warehousing and Storage Cost Calculation Question

[rick151]

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?

The formula being used for the "Free Time Ends" column is =IF(E2="","",E2+15-1)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]

<colgroup><col width="96"></colgroup><tbody>
[TD="class: xl68, width: 96"]Date Received
[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="84"></colgroup><tbody>
[TD="class: xl68, width: 84"]Date Loaded[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="101"></colgroup><tbody>
[TD="class: xl68, width: 101"]Free Time
Ends[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]January[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]February[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]March[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]April[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 81"]
<colgroup><col width="81"></colgroup><tbody>[TR]
[TD="class: xl67, width: 81"]May[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]June
[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]July
[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]August[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]September[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 81"]
<colgroup><col width="81"></colgroup><tbody>[TR]
[TD="class: xl67, width: 81"]October[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]November[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="81"></colgroup><tbody>
[TD="class: xl67, width: 81"]December[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="96"></colgroup><tbody>
[TD="class: xl66, width: 96"]06/27/18[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 84"]
<colgroup><col width="84"></colgroup><tbody>[TR]
[TD="class: xl66, width: 84"]11/28/18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<colgroup><col width="101"></colgroup><tbody>
[TD="class: xl67, width: 101, align: right"]7/11/2018[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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