Need cell formula to calculate storage fee based on unloaded date.

[steve400243]

Hello, I am trying to get the correct cell formula to calculate the storage fee for each row.
Rate is $3.50 PER CBM - 6 Free Calendar Days, including Unloaded Date, $50.00 min Charge for next 5 Days, (Day 7 thru 11) -

12th Day - Additional $3.50 per CBM, per day - No Min

TEST SHEET STORAGE.xlsx
	E	F	G										Q	R	S
1
2
3
4	Unloaded On:	Storage 7th Day:	Storage 12th Day:
5	15-Aug-2021	21-Aug-2021	26-Aug-2021
6
7			CBM:											STORAGE:	TOTAL:
8			8.92											$ -	$ -
9			7.5											$ -	$ -
10			3.2											$ -	$ -
11			1											$ -	$ -
12			10.9											$ -	$ -
13			15.89											$ -	$ -
14
Sheet2

Cell Formulas
Range	Formula
F5	F5	=E5+6
G5	G5	=E5+11
S8:S13	S8	=Q8+R8

 

[Misca]

Not sure if I understood your instructions correctly but here's my suggestion.

I start solving this by creating a 3 x 3 cell table for the charges:
The first column has values 0, 6 and 11 on it (the number of days where charges change).
The second column has min charges for each interval and the last column has the daily charges from that day on.

I created named ranges for these columns to make the formula easier to read and undestand.
The columns in the charge table are called "NODs", "MinCharges" and "DailyCharges".
"StorageDays" is a simple formula that counts the days between the unloading date and the last date ( = Last Date - Unloading Date )
And finally I created a named range for the number of free days ( "FreeDays" ).
In my formula the "LDMs" stands for the number of LDMs on the row.

Having said that here's my basic formula:

=MAX(
    INDEX(MinCharges,MATCH(StorageDays,NODs)),
    INDEX(DailyCharges,MATCH(StorageDays,NODs))*LDMs*(MAX(StorageDays-FreeDays,0))
)

I split the formula in rows to make it easier to follow as I try to explain the logic:

Basically it is a simple MAX formula that compares the min charges ( = the first INDEX formula ) and the daily charges ( = the second INDEX formula ).

The MATCH(StorageDays,NODs) on both INDEX formulas finds the row where it takes the value from. The first INDEX takes the value from the MinCharges column and the second on from the DailyCharges column. After that the DailyCharges value is multiplied by the number of LDMs and StorageDays - FreeDays. In case the StorageDays is less than FreeDays I wrapped that in another MAX to make sure we don't get negative values for the number of days.

That's basically all there is to it but as I said in the beginning I'm not sure I understood the instructions correctly.
This would be all there is to it if you can simply compare the daily charges with the min charges.

If you have to do the calculation in two steps ( first for the first 10 days and then for the days from 11 on ) you'd have to use the same formula twice with adjustments on the StorageDays bit.
Or - since you already know the StorageDays in the first part is always 10 or less - you could replace the INDEXes with a couple of IFs and actual cell references:
If StorageDays < FreeDays return 0, else compare the daily charges to the minimum charge.
And if the actual StorageDays is greater than 10 add the daily charges for the StorageDays from the 11th day on.

However, to make your formulas as flexible as possible for the changes in the future, make sure you have all your constants as cell references.

 

[steve400243]

Thank you for the explanation and for looking at this Misca. I appreciate it. My sincere apologies but I left out the Pickup date. This is the date that would stop the clock from calculating the days. This date would be Starting in cell L8.

TEST SHEET.xlsx
	E	F	G					L					Q	R	S	T
1
2
3
4	Devanned On:	Storage 7th Day:	Storage 12th Day:
5	3-Sep-2021	9-Sep-2021	14-Sep-2021
6
7	Mode:	Cartons:	CBM:					Pickup Date:					WHSE FEE:	STORAGE:	TOTAL:
8	FMM	15	10.5										$ 189.00	$ -	$ 189.00
9	FMM	15	10.5					5-Sep					$ 189.00	$ -	$ 189.00
10	FMM	15	10.5										$ 189.00	$ -	$ 189.00
11	FMM	15	10.5					5-Sep					$ 189.00	$ -	$ 189.00
12	FMM	15	10.5					5-Sep					$ 189.00	$ -	$ 189.00
13	FMM	15	10.5										$ 189.00	$ -	$ 189.00
14
15
Sheet2

Cell Formulas
Range	Formula
F5	F5	=E5+6
G5	G5	=E5+11
Q8:Q13	Q8	=G8*18
S8:S13	S8	=Q8+R8

 

[Misca]

I thought you did. In my explanation I call it "Last Date". You won't find it in the actual formula but it's needed to calculate the value of the StorageDays variable ( = Last Date - Unloading Date ).

 

[steve400243]

I thought you did. In my explanation I call it "Last Date". You won't find it in the actual formula but it's needed to calculate the value of the StorageDays variable ( = Last Date - Unloading Date ).

Ah got it - Thank you again, I believe I have what I need now. Appreciate you assistance!