Holiday Planner stopping clashes of holiday?

**RayLavidge1908** · 08-08-2018, 11:26 AM

im trying to create a holiday planner for the year where I can have separate departments on it - if an individual in a department has a holiday booked then his/her colleagues cannot take the same date - I thought conditional formatting but I cannot figure a method out.

Can anyone help steer me to a solution?

**dflak** · 08-08-2018, 04:49 PM

Create an Excel Table with Employee Name and Holiday Date.

Then when another employee puts in for a date use =ISNA(Match(Asking Date, TableName[Date],0)) - this formula evaluates to TRUE if the date is available.

**dflak** · 08-09-2018, 10:40 AM

I can do better.

Here is a spreadsheet that should do what you want.

First, go to the Lookups Sheet and fill in the employee names in the table in Column A. The other tables on this sheet are used by the program. Don't do anything with them, yet.

On the Scheduler Sheet, Cell B2 has a drop-down list of the employee names that you set up in table on the Lookups sheet.

Enter the start date and end date for the proposed holiday in cells B3 an B4. Cell B3 has data validation on it that allows entries of dates between today and a year from today. Cell B4 has data validation on it that allows entries from the date entered in cell B3 to a year from today. These validations should keep you from attempting to book a holiday in the past or one that ends before it starts.

Click on the Book Holiday Button. This will give you a list of dates from the start date to the end date and whether the day is available.

If any of the proposed dates has a conflict, you will not be able to book the proposed holiday. You can go back and adjust the dates if you want.

If all of the proposed dates are available, you will have a choice to continue the booking or quit. The quit option will let you "test" the available dates. For example you could enter a two-week range to see what dates are available and quit rather than to commit to these dates.

If there is a conflict you can use the pivot table and slicers on the Scheduler Sheet to identify who is currently booked for dates.

Feel free to play with the application and make suggestions for what else you may want to see or bugs you notice.

When you are ready to "go live" with the application, go to the lookup sheet. Select Cell D2 on down to the end of the data. Then RIGHT click and select Delete from the menu. Select Table Row. If you see Entire Row instead, you have a filter on the table, remove it. Once you clear the Green Table, you are good to go.