tracyms
asked on
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!
Available_Project_Dates.xls
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Welcome, glad that helped.
»bp
»bp
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.
Available_Project_Dates_v3.141592658.xls
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.
Available_Project_Dates_v3.141592658.xls
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 :)
ASKER
Thank you Ben! I appreciate the detailed information and screenshot. I can see where this would be useful as well.
Tracy
Tracy
ASKER
I think that'll do just fine! Thank you!