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?
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 PrewCommented:
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 ConnectWise

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
 
ShumsDistinguished Expert - 2017Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ShumsDistinguished Expert - 2017Commented:
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 PrewCommented:
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 gentleman!!!
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.