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

tracyms
tracyms used Ask the Experts™
on
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!
Available_Project_Dates.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT / Software Engineering Consultant
Top Expert 2016
Commented:
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.

Available_Project_Dates.xlsx


»bp

Author

Commented:
Hi Bill,

I think that'll do just fine! Thank you!
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Welcome, glad that helped.


»bp
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer

Commented:
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.


2019-05-02-15_27_45-Available_Projec.png
 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.

Available_Project_Dates_v3.141592658.xls
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer

Commented:
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 :)

Author

Commented:
Thank you Ben! I appreciate the detailed information and screenshot. I can see where this would be useful as well.

Tracy

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial