# Thread: Conditional Formatting Formula (MS Excel 2003)

1. ## Conditional Formatting Formula (MS Excel 2003)

I can't figure out the equation for a conditional formatting formula. I have a start date and a completion date. I want the completion date cell to format green if the completion date is within 2 years of the start date. I have attached a small table with data and formatting that is wrong, but just requires a formula change.

2. ## Re: Conditional Formatting Formula (MS Excel 2003)

In cell B2 set the conditional formatting formula to:

=DATEDIF(\$A2,\$B2,"y")<=2

and copy the format down.

3. ## Re: Conditional Formatting Formula (MS Excel 2003)

Okay, that worked...almost. I changed the date criteria to less than 25 months. For some reason that does a better job than less than or equal to 2 years. I don't really understand that.

However, if I have blank cells in column A, how do I turn off the formatting? Take a look at the new attachment.

4. ## Re: Conditional Formatting Formula (MS Excel 2003)

The 2 years is very literal and works witht he same month and even the day. What dates did the 2 years not work for?

As to eliminating blanks:
=AND(B2<>"",DATEDIF(\$A2,\$B2,"m")<25)

Steve

5. ## Re: Conditional Formatting Formula (MS Excel 2003)

I didn't apply it to the example data table that I provided. I needed help getting started. I used the formula with some additional criteria. Using months and years usually brings back a weird value. For instance, less than or equal to 2 years returns anything that is a day short of 3 years. I am just converting everything to days ("d") and using the 365.24 as the year value.

6. ## Re: Conditional Formatting Formula (MS Excel 2003)

You could use something like this, I'm not sure it will yield a more precise result from =DATEDIF(start,complete,"d")

=AND(ISNUMBER(B2),DATE(YEAR(B2)-2,MONTH(B2),DAY(B2))>A2)

7. ## Re: Conditional Formatting Formula (MS Excel 2003)

<hr> For instance, less than or equal to 2 years returns anything that is a day short of 3 years.<hr>

And this is to be expected. I am 49 yrs old and will be 49 until the end of this year on my brithday. Age is a "step function" and DATEDIF is a reflection of that.. [You could use <2 years and just add 1 to the comparison...]

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•