Excel double condition formula

[Retroshift]

Hi,

I am looking for an Excel non-VBA function formula based on two parameters.
If a cell holds the value 10, then a date starting from 24 December is added, but only if this date is a workday.
If this date is not a workday, then it takes the first day before this date that is a workday.

If the cell holds the value 9, then two dates retrochronologically starting from 24 December downward are added, under the same condition that they should be workdays.
If the cell holds the value 8, then three dates retrochronologically starting from 24 December downward are added, under the same condition that they should be workdays.

I started a formula (=IF(AND(E4=10,C5=1),A5,IF(C4=1,A4,""))) but it does not seem to capture the inherently intertwining logic of all the parameters.

codeblad excel met macro's.xlsm
	A	B	C	D	E	F
1	Date(s) to be added	Eligible extra days	Workday = 1 (parameter)
2	21/12/2022		1
3	22/12/2022		1		Parameter
4	23/12/2022		1		10
5	24/12/2022	23/12/2022	0
6					The cell above can be 10 or 9 or 8
7
Blad1

Cell Formulas
Range	Formula
A2	A2	=DATE(YEAR(TODAY()),12,21)
A3	A3	=DATE(YEAR(TODAY()),12,22)
A4	A4	=DATE(YEAR(TODAY()),12,23)
C2:C5	C2	=IF(WORKDAY(A2-1,1)=A2,1,0)
A5	A5	=DATE(YEAR(TODAY()),12,24)
B5	B5	=IF(AND(E4=10,C5=1),A5,IF(C4=1,A4,""))

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel double condition formula
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Retroshift]

So far, I have this formula but it does not take into account that 2 dates should be added if parameter B is 9 and 3 dates if parameter B is 8 (under the condition that it should be workdays in column B).
Any help with completing this formula would be greatly appreciated.

=IF(AND(E4=10,INDEX(A2:A5,MATCH(1,B2:B5,0))),INDEX(A2:A5,MATCH(1,B2:B5,0)),IF(AND(E4=9,INDEX(A2:A5,MATCH(1,B2:B5,0))),INDEX(A2:A5,MATCH(1,B2:B5,0)),IF(AND(E4=8,INDEX(A2:A5,MATCH(1,B2:B5,0))),INDEX(A2:A5,MATCH(1,B2:B5,0)),"")))

codeblad excel met macro's.xlsm
	A	B	C	D	E	F
1	Date(s) to be added	Workday = 1 (parameter A)	Eligible extra days
2	24/12/2022	0	23/12/2022
3	23/12/2022	1			parameter B
4	22/12/2022	1			10
5	21/12/2022	1
6					The cell above can be 8, 9 or 10.
7
Blad1

Cell Formulas
Range	Formula
A2	A2	=DATE(YEAR(TODAY()),12,24)
B2:B5	B2	=IF(WORKDAY(A2-1,1)=A2,1,0)
C2	C2	=IF(AND(E4=10,INDEX(A2:A5,MATCH(1,B2:B5,0))),INDEX(A2:A5,MATCH(1,B2:B5,0)),IF(AND(E4=9,INDEX(A2:A5,MATCH(1,B2:B5,0))),INDEX(A2:A5,MATCH(1,B2:B5,0)),IF(AND(E4=8,INDEX(A2:A5,MATCH(1,B2:B5,0))),INDEX(A2:A5,MATCH(1,B2:B5,0)),"")))
A3	A3	=DATE(YEAR(TODAY()),12,23)
A4	A4	=DATE(YEAR(TODAY()),12,22)
A5	A5	=DATE(YEAR(TODAY()),12,21)