# Workday formula-Holidays with criteria

#### Masha92

##### New Member
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.

THanks,
MASHA

#### Attachments

• example.PNG
17.3 KB · Views: 7

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### jasonb75

##### Well-known Member
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

##### Well-known Member
T202103a.xlsm
ABCDE
1A1-Jan-20StockB
2A2-Jan-20Date31-12-19
3A3-Jan-201-Jan-20
4A4-Jan-20
5A6-Jan-20
6
7B5-Jan-20
8B
9B
10B
11B
12
13C7-Jan-20
14C8-Jan-20
15C9-Jan-20
1a
Cell Formulas
RangeFormula
E3E3=WORKDAY(E2,1,CHOOSE(MATCH(E1,{"A","B","C"},0),HolidaysA,HolidaysB,HolidaysC))
Named Ranges
NameRefers ToCells
HolidaysA='1a'!\$B\$1:\$B\$5E3
HolidaysB='1a'!\$B\$7:\$B\$11E3
HolidaysC='1a'!\$B\$13:\$B\$17E3

#### jasonb75

##### Well-known Member
With the original sheet layout.
Office 365
Excel Formula:
``=WORKDAY(I2,1,FILTER(B2:B10,A2:A10=I1))``
Excel 2019 or older, array confirmed with Ctrl Shift Enter.
Excel Formula:
``=WORKDAY(I2,1,IF(A2:A10=I1,B2:B10,0))``

#### Masha92

##### New Member

T202103a.xlsm
ABCDE
1A1-Jan-20StockB
2A2-Jan-20Date31-12-19
3A3-Jan-201-Jan-20
4A4-Jan-20
5A6-Jan-20
6
7B5-Jan-20
8B
9B
10B
11B
12
13C7-Jan-20
14C8-Jan-20
15C9-Jan-20
1a
Cell Formulas
RangeFormula
E3E3=WORKDAY(E2,1,CHOOSE(MATCH(E1,{"A","B","C"},0),HolidaysA,HolidaysB,HolidaysC))
Named Ranges
NameRefers ToCells
HolidaysA='1a'!\$B\$1:\$B\$5E3
HolidaysB='1a'!\$B\$7:\$B\$11E3
HolidaysC='1a'!\$B\$13:\$B\$17E3
Hi Dave, thanks for your try. However, this solution is not viable unfortunately. The holidays and the stocks are 5K rows and mixed.

#### Masha92

##### New Member
With the original sheet layout.
Office 365
Excel Formula:
``=WORKDAY(I2,1,FILTER(B2:B10,A2:A10=I1))``
Excel 2019 or older, array confirmed with Ctrl Shift Enter.
Excel Formula:
``=WORKDAY(I2,1,IF(A2:A10=I1,B2:B10,0))``
I am using 365 and this works perfect!! Thanks a lot.

#### Masha92

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

#### jasonb75

##### Well-known Member
You're welcome!

Thanks for the feedback

Replies
6
Views
109
Replies
1
Views
52
Replies
7
Views
169
Replies
3
Views
382
Replies
6
Views
143

1,140,924
Messages
5,703,182
Members
421,280
Latest member
Jaycee01

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back