Workday formula-Holidays with criteria

[Masha92]

Hello all,

I want to make a workday formula where the holidays are kind of dynamic or with specific criteria
I want a workday formula with a condition for the stock name. Workday(date,+1,Holidays!C:C)

The tab Holidays has Column C the holidays, and column B the a code.

I need to use the The workday formula in a way to filter the holidays in column C based on a specific code.

I attached a hypothetical example for better understanding.

Can you please help?

THanks,
MASHA

 

[jasonb75]

Which version of excel are you using? Please update your account details to show the correct version.

This can be done in any version of excel, but would be easier if you have an up to date version of office 365 with the FILTER function.

 

[Dave Patton]

T202103a.xlsm
	A	B	C	D	E
1	A	1-Jan-20		Stock	B
2	A	2-Jan-20		Date	31-12-19
3	A	3-Jan-20			1-Jan-20
4	A	4-Jan-20
5	A	6-Jan-20
6
7	B	5-Jan-20
8	B
9	B
10	B
11	B
12
13	C	7-Jan-20
14	C	8-Jan-20
15	C	9-Jan-20
1a

Cell Formulas
Range	Formula
E3	E3	=WORKDAY(E2,1,CHOOSE(MATCH(E1,{"A","B","C"},0),HolidaysA,HolidaysB,HolidaysC))

Named Ranges
Name	Refers To	Cells
HolidaysA	='1a'!$B$1:$B$5	E3
HolidaysB	='1a'!$B$7:$B$11	E3
HolidaysC	='1a'!$B$13:$B$17	E3

 

[jasonb75]

With the original sheet layout.
Office 365

=WORKDAY(I2,1,FILTER(B2:B10,A2:A10=I1))

Excel 2019 or older, array confirmed with Ctrl Shift Enter.

=WORKDAY(I2,1,IF(A2:A10=I1,B2:B10,0))

 

[Masha92]

T202103a.xlsm
	A	B	C	D	E
1	A	1-Jan-20		Stock	B
2	A	2-Jan-20		Date	31-12-19
3	A	3-Jan-20			1-Jan-20
4	A	4-Jan-20
5	A	6-Jan-20
6
7	B	5-Jan-20
8	B
9	B
10	B
11	B
12
13	C	7-Jan-20
14	C	8-Jan-20
15	C	9-Jan-20
1a

Cell Formulas
Range	Formula
E3	E3	=WORKDAY(E2,1,CHOOSE(MATCH(E1,{"A","B","C"},0),HolidaysA,HolidaysB,HolidaysC))

Named Ranges
Name	Refers To	Cells
HolidaysA	='1a'!$B$1:$B$5	E3
HolidaysB	='1a'!$B$7:$B$11	E3
HolidaysC	='1a'!$B$13:$B$17	E3

Hi Dave, thanks for your try. However, this solution is not viable unfortunately. The holidays and the stocks are 5K rows and mixed.

 

[Masha92]

With the original sheet layout.
Office 365

=WORKDAY(I2,1,FILTER(B2:B10,A2:A10=I1))

Excel 2019 or older, array confirmed with Ctrl Shift Enter.

=WORKDAY(I2,1,IF(A2:A10=I1,B2:B10,0))

I am using 365 and this works perfect!! Thanks a lot.

 

[Masha92]

Just wanted to thank you again! I am using this in all the templates!
Appreciated Jason

 

[jasonb75]

You're welcome!

Thanks for the feedback