the commuter's eye

Highlighting Deadlines in Excel with Conditional Formatting

Posted in help, how tos by martzipan on August 6, 2014

The problem is extremely simple, yet somewhat arcane with Excel. Basically, I want Excel to highlight my dates when a deadline approaches, like a color-codified countdown. The logic behind is:

If the deadline:

  • has passed, then do nothing
  • is two weeks from now, then highlight it in red
  • is two or three weeks from now, then highlight it in yellow
  • is three weeks or more from now, then highlight it in green.

In Excelish, this translates as:

IF cell Value IS less than     =TODAY()+0                 THEN No Format

IF cell Value IS between       =TODAY()+0 AND =TODAY()+14 THEN Format Red

IF cell Value IS between       =TODAY()+15 AND =TODAY()+21 THEN Format Yellow

IF cell Value IS greater than   =TODAY()+22                 THEN Format Green

Out of SLA

Out of SLA

The result looks like this:

Results Highlight

Results Highlight

Advertisements

One Response

Subscribe to comments with RSS.

  1. SutoCom said, on August 10, 2014 at 13:48

    Reblogged this on SutoCom Solutions.


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: