Excel 2007 Conditional Formatting - Based on Date?

Excel 2007: Need a conditional format to highlight row 7 red when F7 is X days away from D7.

D7 “Last Contact” This is a date cell

F7 “Remind” (List cell with number 1,3,7,10 and such)

Sheet2!$B$3 Contains current date TODAY()

Here is how I am using this. D7 “Last Contact” has a date. F7 “Remind” is a drop down list that contains number of days to be reminded in. Say I choose 10 for F7 “Remind”. It will then compare to D7 “Last Contact” and on the 10th day turn the row red.

A million thanks for any and all assistance.

Scott

Salt Lake City, Utah

3 Answers

Relevance
  • Tim
    Lv 4
    1 decade ago
    Favorite Answer

    You don't need the Today() function on the other sheet since you can use it in your conditional format formula. I'm assuming that you already know how to create your data validation list in column F.

    Select the range of cells that for which you want the rule to apply (say, A7:F25, assuming that you have a whole list of contacts in that range). Go to Home and click Conditional Formatting in the Styles group. Click on New Rule and the click the last item in the list of rule types (Use a formula...). Now, under "Format values where this formula is true" type this formula:

    =TODAY()-$F7>=$D7

    Set your format (red background) and then click OK. This will apply the conditional format to each row in the range, depending on the last contact date and the number of days that you have specified in that row.

    Tim

    http://www.tvmcalcs.com

    • Commenter avatarLogin to reply the answers
  • 1 decade ago

    1.) Select the range of cells that you want to have conditional formatting.

    I recommend that you do not select the entire sheet, nor the entire column/row. Instead select the desired table (region). [Conditional Formatting slows down the calculations in your spreadsheet. It is best to minimize the cells affected.] For purposes of illustration, I will assume that you have column headers in row 1, that your table consists of an array of data in A2:Z100, and that there are no locked cells.

    2.) On the menu bar, go to Format | Conditional formatting.

    You already knew this step. :-)

    3.) In the first drop-down menu, select "Formula Is."

    Your desired results require more manipulation than the "Cell Is" option allows.

    4.) Type the following formula:

    =IF ( SUM ( $D2, $F2 ) = TODAY (), TRUE, FALSE )

    Spaces are included so that Y!A does not truncate the formula, and so that you can easily read it. Do not actually include spaces in your formula. Note that I referenced row 2 because you have a range of cells selected that begins on row 2. If the only row you have selected is row 7, as in your example, then all the references to row 2 should instead be row 7.

    5.) Click the "Format..." button, and select the desired formatting

    This is where you select the desired format if the reminder date is equal to today. Per your description, I would choose bolded white text, and a solid red pattern. You may find that bolded red text or pink shading is a little easier on the eyes.

    6.) Click "OK" when you are done selecting your formatting.

    7.) Click "OK" on the Conditional Formatting dialog box.

    This should automatically apply your conditional formatting.

    • Commenter avatarLogin to reply the answers
  • Anonymous
    4 years ago

    One point: it doesn't matter how the cells are formatted, it depends on the value that is formatted. 12/15/2010 and 12/16/2010 will both show, with your format, as 12-10, but will not be equal. You would have to check =and(year(a2)=year($a$12),month(a2)=mont...

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.