Excel Dates - Make Them Speak!

Today I created a sheet in my Data Science workbook called "Drills" - things I want to be able to do efficiently. The columns are # (serial number), What, How (in case you need to peek :), Remarks and When (the last time). The thought occurred to me that I want the dates in this When column to stand out - if it's been more than a week since I did something, I want a particular color and if it's been more than two weeks, I want an   :)

So, with these instructions, you'll be able to set up your Table such that a column with dates will show entries that are 

1. Normal font and fill if within a week from today
2. Light orange fill and normal font if between a week and two weeks from today
3. Red fill and inverted font if older than two weeks from today
4. Apply to all entries in the column as the table grows without you having to make any updates related to the Conditional Formatting (nothing like fire-and-forget, right?:)

Note that, when you click in a Table that you created using "Format as Table" (which is what I recommend always) you get a menu-bar item called "Design" under a "Table Tools" Header. If you go there, you'll see, on the extreme left, a field called "Table Name" with something like "Table1". It's not a bad idea to give this table a unique name. In my case, the Table is called "Drills" and the column in question is "When". So keeping that in mind will help with the instructions below.

Click in one cell in this column and then, with the Home menu items visible (Home as in File, Home, Insert, Page Layout, etc), click on Conditional Formatting and then New Rule

In the New Formatting Rule dialog box, in the "Select a Rule Type:" pane, click on "Format only cells that contain". Then, In the "Edit the Rule Description" pane, for the first and second fields to the right of "between", enter (never mind that my picture shows the 7,14 case)

=TODAY()-14

and

=TODAY()-10000

and then click the "Format..." button (it looks like a button doesn't it :)?

In the Format Cells dialog :

You'll need to click on Strikethrough a couple of times to clear it and then, select Bold for Style. Where it shows Automatic for Color, change that to the color of choice (white) using the drop down. Then go to the Fill tab and choose a color and THEN, click OK.

You'll then find the "New Formatting Rule" dialog showing the format :

Click OK.

Congratulations! You have just set up the rule for getting a date to scream if it's more than two weeks old! Can you do the one-to-two-weeks rule yourself? (You do have a hint in one of the previous pictures:)

Both rules done, now, once again, make your way back to the Conditional Formatting drop down on the Home Menu section and choose "Manage Rules".

You will see that the "Applies to" fields show just the cell that you had selected while you set up the rules. For example, =$F$4 -- meaning an absolute reference to F4.

In my case, all I had to do was change this to =Drills[When]

and that was it. Add rows to your table, stuff continues to work!

Thanks : Allen Wyatt