Calculate Target Date based on DDL option

[SaraWitch]

Hi peeps,

I am tying myself up in knots trying to calculate a target date based on a DDL selection in another cell. I've included the formulas I've tried to show what a pickle I'm getting in!

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Agreed Time Frame	Date given	Target complete date (working days minus bank holidays)	Should be						BH 2024/25
2	A	01/08/2024	=IF(OR(A2="A",NETWORKDAYS($B2,5,BH2024_25),IF(A2="B",NETWORKDAYS($B2,10,BH2024_25),IF(A2="C",NETWORKDAYS($B2,10,BH2024_25),IF(A2="D",NETWORKDAYS($B2,100,BH2024_25,""))))))	08/08/2024						01/04/2024
3	B	02/08/2024	=IF(OR(A3="A",NETWORKDAYS($B3+5-BH2024_25),IF(A3="B", NETWORKDAYS($B3+10-BH2024_25), IF(A3="C",NETWORKDAYS($B3+15-BH2024_25), IF(A3="D",NETWORKDAYS($B3+100-BH2024_25),"")))))	16/08/2024						06/05/2024
4	C	07/08/2024	=IF(OR(A2="A",NETWORKDAYS($B2,5,BH2024_25),A2="B",NETWORKDAYS($B2,10,BH2024_25),A2="C",NETWORKDAYS($B2,10,BH2024_25),A2="D",NETWORKDAYS($B2,100,BH2024_25),""))	28/08/2024						27/05/2024
5	D	13/08/2024		06/01/2025						26/08/2024
6										25/12/2024
7	A=Date given + 5 working days (minus bank holidays)									26/12/2024
8	B=Date given + 10 working days (minus bank holidays)									01/01/2025
9	C=Date given + 15 working days (minus bank holidays)
10	D=Date given + 100 working days (minus bank holidays)
Target Date Based On DDL

Cells with Data Validation
Cell	Allow	Criteria
A2:A5	List	A,B,C,D

Any help would be greatly appreciated...

 

[SaraWitch]

I've been considering alternative ways and a VLOOKUP works - I just need to minus the bank holidays:

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Agreed Time Frame	Date given	Target complete date (working days minus bank holidays)	Should be						BH 2024/25		Agreed Time Frame
2	A	01/08/2024	08/08/2024	08/08/2024						01/04/2024		A	5
3	B	02/08/2024	16/08/2024	16/08/2024						06/05/2024		B	10
4	C	07/08/2024	28/08/2024	29/08/2024						27/05/2024		C	15
5	D	13/08/2024	31/12/2024	06/01/2025						26/08/2024		D	100
6										25/12/2024
7	A=Date given + 5 working days (minus bank holidays)									26/12/2024
8	B=Date given + 10 working days (minus bank holidays)									01/01/2025
9	C=Date given + 15 working days (minus bank holidays)
10	D=Date given + 100 working days (minus bank holidays)
Target Date Based On DDL (2)

Cell Formulas
Range	Formula
C2:C5	C2	=IF(A2="","",WORKDAY(B2,VLOOKUP(A2,ATF,2,0)))

Named Ranges
Name	Refers To	Cells
'Target Date Based On DDL (2)'!ATF	='Target Date Based On DDL (2)'!$L$2:$M$5	C2:C5

Cells with Data Validation
Cell	Allow	Criteria
A2:A5	List	A,B,C,D

I'm sure it's really simple, but any help would be welcomed

 

[Asbestos_Jen]

I put your Agreed Time Frames into a table in A12:B15 and calculated the Target Date with an index,match in C2:C5. Is this what you're looking for?

Book1
	A	B	C	D	E					J
1	Agreed Time Frame	Date given	Target complete date (working days minus bank holidays)	Should be						BH 2024/25
2	A	1-Aug-24	8-Aug-24	8-Aug-24						1-Apr-24
3	B	2-Aug-24	16-Aug-24	16-Aug-24						6-May-24
4	C	7-Aug-24	29-Aug-24	28-Aug-24						27-May-24
5	D	13-Aug-24	6-Jan-25	6-Jan-25						26-Aug-24
6										25-Dec-24
7	A=Date given + 5 working days (minus bank holidays)									26-Dec-24
8	B=Date given + 10 working days (minus bank holidays)									1-Jan-25
9	C=Date given + 15 working days (minus bank holidays)
10	D=Date given + 100 working days (minus bank holidays)
11
12	A	5
13	B	10
14	C	15
15	D	100
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=WORKDAY(B2,INDEX($A$12:$B$15,MATCH(A2,$A$12:$A$15,0),2),BH2024_25)

 

[Asbestos_Jen]

Your VLOOKUP will work also, you just need to add your holiday table into the workday formula:

=IF(A2="","",WORKDAY(B2,VLOOKUP(A2,ATF,2,0),BH2024_25))

 

[SaraWitch]

Brilliant! Thank you so much, @Asbestos_Jen! They both work perfectly!

 

[Asbestos_Jen]

Glad to have helped, and thanks for the feedback.