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

 

[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

 

[tusharm]

Yes, you are right. I only addressed your first example. Sorry about that.

Let's start from the beginning. There are five possibilities in comparing the storage period (SP) with the billing period (BP).
The SP is completely inside the BP.
The BP is completely inside the SP.
The SP overlaps the first part of the BP.
The SP overlaps the latter part of the BP.
The SP and the BP don't have anything to do with each other.
We also have to cater to the possibility that no out-date is available.
So, we need four terms in the MIN statement to cater to the first 4 possibilities and an IF clause to deal with the last two.

Add
out-date - period-start-date
for the condition I missed.

So, you really need
=IF(OR(Out="",Out<PStart,In>PEnd),0,1+MIN(PEnd-PStart,Out-PStart,Out-(In+1),PEnd-(In+1))) * out-CBM * daily-rate-per-CBM.

I defined those names (Insert | Name > Define...) using a combination of relative and absolute addresses. See the next post for a HTMLMaker version of the spreadsheet.

The 1+ and the In+1 adjust for what I *believe* -- based on your example -- to be how you calculate the number of days.

This covers all of the possibilities -- I think.

_________________
Regards,

Tushar
www.tushar-mehta.com
This message was edited by tusharm on 2002-08-14 08:12

 

[tusharm]

Book1
	A	B	C	D	E	F	G	H
1	In	Out	CBM	Pstart			$/cbm/day	3
2				21-Jun	21-Jul
3				20-Jul	20-Aug
4	15-Jul	19-Aug	25	5	30		In	=Sheet2!$A4
5	15-Jul	05-Sep	25	5	31		Out	=Sheet2!$B5
6	01-Jul	03-Jul		2	0		PEnd	=Sheet2!G$3
7	10-May	20-Jun		0	0		PStart	=Sheet2!G$2
8	23-Jul	02-Aug	53	0	10		AbovenamesarevalidwhenG4
9							istheactivecell
Sheet2