# 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})))
``````
LVL 1
###### Who is Participating?

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.

IT / 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

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

Excel & 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
``````
See the screen shot below, which excludes Sundays & Holidays (04-Jan/10-Jan/15-Jan)
0
Information 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
Excel & 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
IT / 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.