IF CONDITIONS BY DATE

[BOAGS46]

Hi

Hoping someone can assist please. This query is actually for application in a SharePoint site, however I am using an Excel example to find out the solution so it can then be applied back into SharePoint.

I have four columns that calculate import duty on cigarettes and tobacco with the duty rate changing every 6 months. The columns are:
Column A: IMPORT DATE - in DD/MM/YYYY format
Column B: COMMODITY - input as either "CIGARETTES" or "TOBACCO"
Column C: QUANTITY
Column D: DUTY - calculated using =IF(B2="CIGARETTES",C2*1.1404,IF(B2="TOBACCO",C2*1629.14,0)) for Row 2

On 01/03/2023, the duty rates changed, so the duty calculation formula became =IF(B5="CIGARETTES",C5*1.6435,IF(B5="TOBACCO",C5*1663.36,0)) for Row 5

Is there a formula that before 01/03/2023, =IF(B2="CIGARETTES",C2*1.1404,IF(B2="TOBACCO",C2*1629.14,0)) is used and after/including 01/03/2023, =IF(B5="CIGARETTES",C5*1.6435,IF(B5="TOBACCO",C5*1663.36,0)) is used?

As mentioned, the duty rate changes every 6 months, so can the formula please include the ability to use a new IF condition to calculate the duty for dates after 01/09/2023?

Thanking you in advance.

 

[BrianM45]

Hi BOAGS46

Normally, with this kind of formula which is in French, I do not know the instructions in English, sorry
In D2

=C2*SOMME.SI.ENS(J$2:J$5;I$2:I$5;B2;G$2:G$5;"<=" & A2;H$2:H$5;">=" & A2)

One more

 

[awoohaw]

try this, I created a lookup table so you can add duties over time. The column DUTY is your first formula repeated. Column E is what I'm asking you to consider.

Book1
	A	B	C	D	E	F	G	H	I
1	IMPORT DATE	COMMODITY	QUANTITY	DUTY			Start Duty Date	COMMODITY	Duty
2	2022-03-02	TOBACCO	30	48874.2	48874.20		2022-01-01	CIGARETTES	1.1404
3	2022-09-15	TOBACCO	30	48874.2	49900.80		2022-01-01	TOBACCO	1629.14
4	2023-04-05	TOBACCO	30	48874.2	50948.96		2022-07-01	CIGARETTES	1.6435
5	2022-07-05	CIGARETTES	4500	5131.8	7395.75		2022-07-01	TOBACCO	1663.36
6	2022-04-30	CIGARETTES	4500	5131.8	5131.80		2023-01-01	CIGARETTES	0.041
7	2023-03-05	CIGARETTES	4500	5131.8	184.50		2023-01-01	TOBACCO	1698.299
8	2023-08-05	CIGARETTES	4500	5131.8	0.00
Sheet3

Cell Formulas
Range	Formula
D2:D8	D2	=IF(B2="CIGARETTES",C2*1.1404,IF(B2="TOBACCO",C2*1629.14,0))
E2:E8	E2	=C2*SUM((--(EOMONTH(A2,-MONTH(A2)+(INT(MONTH(A2)/6)*6))+1=$G$2:$G$7))*(--(B2=$H$2:$H$7))*($I$2:$I$7))

 

[BOAGS46]

Hi BOAGS46

Normally, with this kind of formula which is in French, I do not know the instructions in English, sorry
In D2

=C2*SOMME.SI.ENS(J$2:J$5;I$2:I$5;B2;G$2:G$5;"<=" & A2;H$2:H$5;">=" & A2)

View attachment 86751

One more

Thank you BrianM45. I will try your solution to see how it goes. I appreciate your time.
Kind regards
Greg

 

[BOAGS46]

try this, I created a lookup table so you can add duties over time. The column DUTY is your first formula repeated. Column E is what I'm asking you to consider.

Book1
	A	B	C	D	E	F	G	H	I
1	IMPORT DATE	COMMODITY	QUANTITY	DUTY			Start Duty Date	COMMODITY	Duty
2	2022-03-02	TOBACCO	30	48874.2	48874.20		2022-01-01	CIGARETTES	1.1404
3	2022-09-15	TOBACCO	30	48874.2	49900.80		2022-01-01	TOBACCO	1629.14
4	2023-04-05	TOBACCO	30	48874.2	50948.96		2022-07-01	CIGARETTES	1.6435
5	2022-07-05	CIGARETTES	4500	5131.8	7395.75		2022-07-01	TOBACCO	1663.36
6	2022-04-30	CIGARETTES	4500	5131.8	5131.80		2023-01-01	CIGARETTES	0.041
7	2023-03-05	CIGARETTES	4500	5131.8	184.50		2023-01-01	TOBACCO	1698.299
8	2023-08-05	CIGARETTES	4500	5131.8	0.00
Sheet3

Cell Formulas
Range	Formula
D2:D8	D2	=IF(B2="CIGARETTES",C2*1.1404,IF(B2="TOBACCO",C2*1629.14,0))
E2:E8	E2	=C2*SUM((--(EOMONTH(A2,-MONTH(A2)+(INT(MONTH(A2)/6)*6))+1=$G$2:$G$7))*(--(B2=$H$2:$H$7))*($I$2:$I$7))

Thank you also awoohaw. I will try your solution also to see how it goes. I too appreciate your time.
Kind regards
Greg

 

[awoohaw]

best wishes with it.