Very tough - storage fee calculation

[sony]

dear pls see the table

Only useful columns for calculation are only:
Column F : Date-in
Column G : Date-out
Column L : Volume out (cbm)

Charging Period : 21st to 20th of every month

I wonder if VBA codes can help calculate storage fee for each charging period and put in the Column M & N, O..n Etc I produce the idea result in the column M, N in red colour for the amount. you can refer to

charging formula is
US$ 3 X VolumeOut (cbm) X Storage days
(Storage Days = Date-out Less Date-In)


the difficult tings are
1) there can be many date-out records for each Date-in shipment. e.g. look at D8749, D8752 they are two deliery out for the shipemnts)

2) If the storage period cross many charging period, excel will auto put the amt in column O, P, R, S, etc.

INVENTORY REPORT1.xls
	D	E	F	G	H	I	J	K	L	M	N
8	PO#(SR#)/DOno.	Vendorname	Date		Quantity(PC)			Volume(CBM)
9			In	Out	In	Out	Balance	In	Out	JuneCharges21stJuneto20thJuly)	JulyCharges21stJulyto20thAug)
10
11	00002425(AF27FI04)	ZDL	7/15/2002		7200		7200	50
12	D8749			8/19/2002		3600			25	$375	$2250
13	D8752			9/5/2002		3600			25	$375	$2250
14
15	00002425(AF27FI04)	ZDL	7/16/2002		7245		7245	53.47
16	D8749			8/2/2002		7245	0		53.47
17
18	00002425(AF27FI04)	ZDL	7/17/2002		6809		6809	50.67
19	D8749			8/2/2002		6809	0		53.47
20
21	00002426(AF27FG08)	ZDL	7/18/2002		12600		12600	50.96
22	D8749			8/2/2002		12600	0		50.96
23
24	00002426(AF27FG08)	ZDL	7/23/2002		7000		7000	27.89
25	D8749			8/2/2002		7000	0		27.89
26
AUG

 

[tusharm]

If you duplicate the in date on the row that contains the out information, the calculation becomes pretty simple. Something like the untested<pre>MIN(out-date - in-date, period-end-date - period-start-date, period-end-date - in-date)* out-CBM * daily-rate-per-CBM.</pre>Basically, in your layout, the only 'challenge' is figuring out the in-date that corresponds with each out-date.

 

[sony]

Dear Tusharm, than you for your kindness in reading & replying my question.
But the answer looks not correct

e.g. if shipment is in 7/15 & out 8/19
suppose the June period (6/21 to 7/20) only count 5 days in M12 whereas July period (7/21 to 8/20) counts 30 days in N12

but the formula cannot produce the correct no of days in its perodnot to say calculate the fees.

Could you please help me again

 

[sony]

Dear Tusharm, than you for your kindness in reading & replying my question.
But the answer looks not correct

e.g. if shipment is in 7/15 & out 8/19
suppose the June period (6/21 to 7/20) only count 5 days in M12 whereas July period (7/21 to 8/20) counts 30 days in N12

but the formula cannot produce the correct no of days in its period not to say calculate the fees.

Could you please help me again

 

[sony]

Dear Tusharm,

I got it by adjusting the in & out date in next period, I now got what you meant, sorry for my being stupid. You are genius!

Best wishes

 

[sony]

tusharm, for your information,

storage-fee.xls
	A	B	C	D	E	F	G
1			startperiod	21-Jun-02	21-Jul-02	21-Aug-02	21-Sep-02
2	in-date	ou-date	endperiod	20-Jul-02	20-Aug-02	20-Sep-02	20-Oct-02
3	15-Jul-02	25-Sep-02		5	30	30	5
Sheet1

 

[sony]

tusharm, for your information,

storage-fee.xls
	A	B	C	D	E	F	G
1			startperiod	21-Jun-02	21-Jul-02	21-Aug-02	21-Sep-02
2	in-date	ou-date	endperiod	20-Jul-02	20-Aug-02	20-Sep-02	20-Oct-02
3	15-Jul-02	25-Sep-02		5	30	30	5
Sheet1

 

[sony]

tusharm, for your information,

storage-fee.xls
	A	B	C	D	E	F	G
1			startperiod	21-Jun-02	21-Jul-02	21-Aug-02	21-Sep-02
2	in-date	ou-date	endperiod	20-Jul-02	20-Aug-02	20-Sep-02	20-Oct-02
3	15-Jul-02	25-Sep-02		5	30	30	5
Sheet1

 

[sony]

tusharm, for your information,

storage-fee.xls
	A	B	C	D	E	F	G
1			startperiod	21-Jun-02	21-Jul-02	21-Aug-02	21-Sep-02
2	in-date	ou-date	endperiod	20-Jul-02	20-Aug-02	20-Sep-02	20-Oct-02
3	15-Jul-02	25-Sep-02		5	30	30	5
Sheet1

 

[sony]

tusharm, for your information,

storage-fee.xls
	A	B	C	D	E	F	G
1			startperiod	21-Jun-02	21-Jul-02	21-Aug-02	21-Sep-02
2	in-date	ou-date	endperiod	20-Jul-02	20-Aug-02	20-Sep-02	20-Oct-02
3	15-Jul-02	25-Sep-02		5	30	30	5
Sheet1