Excel Conditional Formatting based on date range - workdays only

Question

I'm pretty new to Excel, and one of my bosses (who knows even less about Excel than I) is asking me to create a spreadsheet that will track a bunch of different deadlines. For quick reference, I need to use conditional formatting to do the following:

1. Highlight only non-blank cells that contain a date in the past.
2. Highlight with a color scale cells containing dates between now and now+90 workdays.

Nothing I've tried is giving me the results I need. I'd appreciate any guidance, as I'm getting very frustrated and this is fairly time-sensitive.

Answer 1

I will answer your second question. I think you will be able to answer the first one yourself from what you learn from the second one.

The executive summary is: use a combination of TODAY() and WORKDAY().

Now for some useful detail.

First, I suspect (from your reputation) that you are new to StackOverflow. Welcome! Take a look on the right side of this page. At the bottom there are "Related" questions. I obtained the following one from there. It is half of the answer to your question. The key point is to use the TODAY() function in your formula.

Second, the WORKDAY() function, with a positive number n, provides a date n workdays into the future. You may also need to make adjustments if your company's calendar is different from the canonical one.

You may want to set a cell in your spreadsheet to the date returned by =WORKDAY(TODAY(),90). Lets just pick A1 for simplicity of explanation. When you first add that formula, you will get what seems like a nonsensical number. Change that cell's format to date and it will make sense.

Now you can refer to A1 in your conditional formatting, as in the "less than" highlighting rule. That should do it.

P.S., while you are at it, search for "named ranges". As a beginner, it is probably the most useful "trick" you could learn. Then name whatever your A1 cell turns out to be something meaningful, like ninetyWorkdaysAhead (you cannot start a named range with a number), then refer to that name in your rule. That way, if you need to move it, your code will not break.