Share via

Need to color code cells with dates > 30, 60, 90 days.

Anonymous
2012-12-12T21:06:40+00:00

Help. I have a need to color code a column of entrered dates to quickly view which are greater than 30, 60, or 90 days old. The optimal automated solution will calculate the number of days between the current date (either computer or hidden cell) and the date entered in the entry's date column cell; finally, the color coding will automatically change when the  difference between the current date and the cell's entry date ages. For example: today's date is 12/12/12 the date in the entry cell is 11/10/12, so the coloring (text or cell) should be yellow; when today's date changes to 01/12/13, the coloring needs to change to orange, when today's date changes to 02/12/13, the coloring needs to change to red.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-12-13T16:05:04+00:00

    Originally, I made certain the new CF rules were above the modify every other line rule (pecking/sequence order). Unfortunately the new rules didn't work either, I'll just insert a column and code that one to compare the Current date to the updated date then color code the delta.

    Thank you for VERY much for your assistance, I've just run out of alloted time to look for an elegant solution.

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-12-12T22:28:28+00:00

    I have found that the best method for this is to select the column of dates and create three rules, each using the Use a formula to determine which cells to format option. Use these three formulas for your progression of dates,

    =AND(ISNUMBER(A1),A1>TODAY()+30,A1<=TODAY()+60)          ◄ yellow fill 

    =AND(ISNUMBER(A1),A1>TODAY()+60,A1<=TODAY()+90)          ◄ orange fill

    =AND(ISNUMBER(A1),A1>TODAY()+90)                                     ◄ red fill 

    Change A1 to the top cell in your date column before pasting these into three separate CF rules.

    More detail on the actual process can be found in my answer in this thread,

    Conditional Formatting By Date

    20+ people found this answer helpful.
    0 comments
  2. Anonymous
    2012-12-13T00:25:09+00:00

    I copied and pasted the formulas into the appropriate option in Conditional Formatting, but the coding did not work--nothing changed with dates of 9/12/12, 10/11/12, and 11/8/12 . . . 

    I think I misread your original request by reversing the conditions. These should have been the three formulas,

    =AND(ISNUMBER(A1),A1<TODAY()-30,A1>=TODAY()-60)          ◄ yellow fill 

    =AND(ISNUMBER(A1),A1<TODAY()-60,A1>=TODAY()-90)          ◄ orange fill

    =AND(ISNUMBER(A1),A1<TODAY()-90)                                     ◄ red fill

    . . . yes, I have the =now() in cell A1.

    I'm not really sure what this is intended to accomplish. Does it 'nudge' the CF rules or something?

    . . . I also have the following formual in the entire worksheet =MOD(ROW(),2)=1

    All things being equal, it is probably better to have these three CF rules above the one governing alternating row highlighting in the CF pecking order.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2012-12-12T23:11:23+00:00

    I copied and pasted the formulas into the appropriate option in Conditional Formatting, but the coding did not work--nothing changed with dates of 9/12/12, 10/11/12, and 11/8/12 . . . yes, I have the =now() in cell A1.

    I also have the following formual in the entire worksheet =MOD(ROW(),2)=1

    To ensure there were zero formatting conflicts, I changed the Format of the =AND... formula to apply to the Text only.

    Any other suggestions?

    0 comments