Exclude Sunday's and Holidays in Formula

I am trying to exclude ALL Sunday's and ALL Holidays from my formula to only show days between Monday & Saturday so I can list out all the work days in my excel spreadsheet. I found the following formula online that says it will exclude both Sunday's and Holidays but it's only excluding Sunday's. The holidays are still showing. What is wrong with this formula and how can I fix this to work correctly? This is an array formula too. I have a named range called Holidays to show all the holidays we have for 2018.

=MIN(IF(WEEKDAY(AG10+1+{0,1,2,3,4})<>1,IF(ISNA(MATCH(AG10+1+{0,1,2,3,4},Holidays,0)),AG10+1+{0,1,2,3,4})))

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
If your intent was to find the next day that was not a holiday or a Sunday, then it seems to be working.  See attached simple test...

EE29080999.xlsx


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsExcel & VBA ExpertCommented:
Hi Lawrence,

I don't think you need Array Formula, If you have first working day in AG10, then enter below formula in AG11 and drag down until last working day of the year.
=MIN(IF(WEEKDAY(AG10+{1,2,3,4,5},2)<7,IF(ISNA(MATCH(AG10+{1,2,3,4,5},Holidays,0)),{1,2,3,4,5})))+AG10

Open in new window

See the screen shot below, which excludes Sundays & Holidays (04-Jan/10-Jan/15-Jan)
List Dates Exclude Sundays & Holidays
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I just tested it out again in a clean workbook and it's doing the Sunday's ok but not the holidays. I have 2/19/18 as a holiday and it's still showing up in my list. See attached file. I did two examples, one in each row and one in each column.
Test.xlsx
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ShumsExcel & VBA ExpertCommented:
Hi Lawrence,

I just tested on your test sheet, it works fine (it excluded 2/19/18), please see attached...Your named range Holidays was from another workbook, this could be reason it was not working.
Lawrence_Test.xlsx
0
Bill PrewIT / Software Engineering ConsultantCommented:
It doesn't look like your Holidays named range is defined correctly, it showed here as below in the file you posted.

='C:\Users\xxxxxxxx\Downloads\Projects\Efficiency Report\[Efficieny Dashboard - Current Year.xlsm]Sheet1'!$A$2:$A$13


»bp
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
UGH! Sometimes it's the little things you overlook! Thank you gentlemen!!!
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.