Calendar excluding days

I have a calender that has holidays marked off, is there a way that I can generate a list to tell me what days are available but exclude the weekends and holidays? I was going to use the workday function but not sure what to put on the days column or if this the best option.

The approach depends on how you've organized your calendar. Can you post it?

A couple of things to think about: Excel does not provide a function that allows you to determine the fill color of a cell. That means that your method of marking holidays by highlighting them would require VBA to detect. You already have a list of holidays; if you intend to leave that in place then I think it would be straightforward to create a list of non-holidays/non-weekends. If you eliminate that list, things get trickier.

Calendars usually work best if you use actual dates for the month names and day numbers, formatted appropriately. This takes a little more effort to set up but allows more automation of things, like being able to make the calendar automatically formatted for any year.

I'm going to go off and think about this one, because I will probably make specific recommendations about how to build your workbook, rather than just a straight answer to your question. Didn't want you to think I dropped out.

There are lots of folks here who have done magic with calendars so hopefully they will add something more concrete here.

Here's one example. This does something a little inside out of what you were asking for. You can list the holidays on the Parameter sheet and they are automatically highlighted on the calendar (instead of trying to generate a list based on what's highlighted). I didn't account for vacation days but the same strategy would apply.

In addition there is a parameter for the year, so if you change the year the calendar will update accordingly.

this is better than what I wanted!!!

That made my day

Not sure what your knowledge base is so not sure how much to explain.

I use the list of holidays to do conditional formatting on the dates. The conditional formatting formula uses MATCH to see if the current date is found in the holiday list, and if so turns the cell yellow. Because conditional formatting cannot refer to cells on other sheets using cell references, I created a named range for the column containing the holiday list (it can refers to named ranges).

You can do the same thing to establish a list of vacation days in a different column, and add another formula to check those dates and use a different color.

If you are not familiar with conditional formatting let me know and I can explain in greater detail. You may want to navigate to conditional formatting and look at what is set up so far.

For those following along at home, I have had a little sidebar with jgomez regarding conditional formatting. To bring it back to the thread I have attached a workbook with the following revisions:

Added a list of vacation days on the Parameter worksheet, and updated the conditional formatting rules on the calendar page to take it into account

Added a header at the top of the calendar page to show the year

Added calculations at the bottom to support the count of vacation days used and remaining