Workday formula-Holidays with criteria

Masha92

New Member
Joined
Jan 27, 2019
Messages
48
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
 

Attachments

  • example.PNG
    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
Joined
Dec 30, 2008
Messages
13,504
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,926
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,504
Office Version
  1. 365
Platform
  1. Windows
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))
 
Solution

Masha92

New Member
Joined
Jan 27, 2019
Messages
48

ADVERTISEMENT

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
Joined
Jan 27, 2019
Messages
48
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
Joined
Jan 27, 2019
Messages
48
Just wanted to thank you again! I am using this in all the templates!
Appreciated Jason
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,504
Office Version
  1. 365
Platform
  1. Windows
You're welcome!

Thanks for the feedback :)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top