# Conditional Formatting using Dates in Excel



## scoochie (Jan 10, 2006)

I am having difficulty finding an approach to activate conditional formatting of a cell that that contains a persons name based on date data entered into two other cells.

What I am trying to achieve is to have the employees name formatted to indicate that they are unavailable for dispatch because the computer's date (today()) is the start date, finish date or any date in between those dates that have been entered elsewhere on the worksheet for their vacation time.

Has anyone else set up anything similar that might work?

Thanks


----------



## ReeKorl (Mar 25, 2005)

Unfortunately, the conditional formatting doesn't allow you to change the formatting of a cell depending on the contents of another cell. If you can live with the cell _next to_ the person's name being coloured green/red then you could try this:

Create a new column directly to the right of the person's name (I'll assume you have the person's name in A column, and the new one in B). This column needs a formula to return true/false if TODAY() is between two dates (which I'll assume you have in C as start date and D as end date). This formula will be

```
=AND(TODAY()>=C1,TODAY()<=D1)
```
 Conditional format the B column to show green if FALSE and red if TRUE (if TRUE then today is in the blocked out dates, therefore red).

If this isn't good enough, you'll need to use some VBA code to do it. Post back if you need this instead and I'll see what I can come up with.


----------



## scoochie (Jan 10, 2006)

*Works well*

Thanks,

As a matter of fact...this formula and method works ideally for what I need and the extra column creation was not needed. I entered the formula into both the first and last name cells for the employee in question, referenced the two cells with the dates in the formula and chose to format the cells with the names to "strike out" the text when their value is true. I assume now than since I can apply up to 3 conditions to a cell, that I could theoretically ad the conditions of "less than 7 days til vacation starts" and less than two days when vacation ends". I am unsure of the formulas to achieve this, however, I am trying.

Thanks so much for your help!


----------



## scoochie (Jan 10, 2006)

ReeKorl,

Your help is immeasurable! Based on your formula, I know have the name cells conditionally formatted based on a true/false condition for 7 days before vacation, vacation, and 2 days before return from vacation based on the dates in two separate cells elsewhere on the work sheet. If any one is interested in the formulas or a sample worksheet, I will post it on request.

Gotta go,

Thanks again!


----------

