Conditional Formatting using Dates in Excel

scoochie · 01-15-2006, 01:32 PM

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** · 01-16-2006, 10:08 AM

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

Code:

=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 · 01-16-2006, 07:52 PM

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 · 01-17-2006, 05:34 AM

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!

01-15-2006, 01:32 PM	#1 (permalink)
scoochie Registered User Join Date: Jan 2006 Posts: 18 OS: xp	Conditional Formatting using Dates in Excel 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

01-16-2006, 10:08 AM	#2 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	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 Code: =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. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

01-16-2006, 07:52 PM	#3 (permalink)
scoochie Registered User Join Date: Jan 2006 Posts: 18 OS: xp	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!

01-17-2006, 05:34 AM	#4 (permalink)
scoochie Registered User Join Date: Jan 2006 Posts: 18 OS: xp	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!