Create List of Available Dates from List of Unavailable Dates and Holidays

Hello Experts,

I'd like to create a list of available dates for employees in excel. We're working on a project and I'm trying to group departments together on the dates they'll be in the office.

I've attached a spreadsheet with an example but need the formula to put it all in place - if not a formula maybe it needs VB code to get it to work as desired?

- I have the dates employees will NOT be available (Unavailable Dates in attached spreadsheet)
- I have a Holidays tab to list Holidays where no one will be available
- I have a weekday calendar that lists all the days of the week

The available dates (Available Dates tab in the spreadsheet) will be produced by looking at the weekday calendar and excluding Dates Unavailable and Holidays. At least that's what I need it to do. Once I get that part working, I can do a pivot table to group the departments on their available dates.

 I hope it all makes sense, thanks!
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 PrewIT / Software Engineering ConsultantCommented:
This is a little different than what you asked, but seems like it could get you where you need to be.  I copied the list of work days to the Available Dates sheet, and duplicated it for each employee.  Then I added a fairly simple formula that for each row flags that employee as available or not available (Y/N).  You can now use the Available Dates sheet as the source for your pivot table, and just filter on the "N" values if you only want to see the unavailable dates for each employee.



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
tracymsAuthor Commented:
Hi Bill,

I think that'll do just fine! Thank you!
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome, glad that helped.

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Ben Personick (Previously QCubed)Lead SaaS Infrastructure EngineerCommented:
You can do this fairly easily using the Excel "WorkDay" Function.

 It allows you to specify a given starting day, and calculates' the next workday, excluding an optional list of holidays you specify.

  In this screenshot you can see how the function just shows all days M to F when used to create a list of workdays, and you can specify a list of "Holidays" to exclude.

 In the example screenshot I am ignoring Holidays in the next (yellow Column) and then, to exclude the remaining holidays I could either do a lookup, or create an aggregate view of the holidays and days out (I chose to do an aggregate set and feed it into the workday function.)

I have amended the spreadsheet to do that aggregation on the "Dates Unavailable" tab, of the unavailable and holiday dates, and I have placed the calculation that determines what workdays ar in the Available Dates Sheet.

I noticed you have employee names there though and this is confusing, as it sounded like you wanted to be able t have a simple list of all days that no employee was not working due to Holiday or day off.

If you wanted to instead have distinct list for each employee tat could be done with some additional work.

Attached is the worksheet with my example Screenshot sheet, this sheet is no longer needed (and the "Weekday sheet" is similarly not needed as we use the "Weekday" Excel function instead.

Edit - Wrong copy of the sheet.  I have amended that.

Ben Personick (Previously QCubed)Lead SaaS Infrastructure EngineerCommented:
lol, oops, I started working ion this hours ago, but that's what happens when you go on to doing other things and then come back to a post you didn't finish writing when your desktop had to be restarted earlier in the day.  Glad to offer my take too, not sure if it's different from Bills as I didn;t see his prior to posting, lmk if helpful even if the Q is solved :)
tracymsAuthor Commented:
Thank you Ben! I appreciate the detailed information and screenshot. I can see where this would be useful as well.

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.