IF function based on dates in cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1999417%22%20slang%3D%22en-US%22%3EIF%20function%20based%20on%20dates%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1999417%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELike%20many%20posts%2C%20relatively%20new%20to%20excel%2C%20though%20I%20have%20some%20working%20experience.%20I'm%20trying%20to%20build%20a%20page%20that%20would%20highlight%20an%20entire%20row%20a%20certain%20color%20if%20a%20date%20is%20too%20far%20removed%20from%20a%26nbsp%3B%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ETODAY%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%20cell.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIts%20basically%20a%20way%20to%20ensure%20that%20tasks%20do%20not%20get%20too%20out%20of%20hand%20and%20I%20could%20visually%20see%20if%20something%20was%20done%202%20weeks%20ago%2C%20and%20it%20should%20have%20been%20done%20within%20a%20week%2C%20then%20I%20would%20have%20a%20visual%20trigger%20to%20show%20that%20that%20task%20needs%20attention.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EI%20built%20my%20excel%20sheet%20so%20that%20tasks%20would%20need%20to%20be%20checked%20off%20in%20a%20linear%20fashion%20along%20the%20same%20row%20across%20multiple%20columns.%20So%20long%20as%20a%20date%20is%20input%20into%20the%20next%20column%20and%20the%20last%20date%20input%20isn't%20too%20far%20removed%20from%20the%26nbsp%3B%3DTODAY()%20cell%2C%20a%20row%20would%20remain%20white.%20However%2C%20once%20it's%20been%20a%20certain%20length%20of%20time%2C%20then%20that%20row%20would%20highlight%20red.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EI%20know%20this%20is%20complicated%2C%20but%20any%20insight%20would%20be%20appreciated.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1999417%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1999583%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20function%20based%20on%20dates%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1999583%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F905619%22%20target%3D%22_blank%22%3E%40ryn8985%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20say%20your%20Date%20column%20is%20Column%20A%20and%20the%20data%20begin%20in%20row%202.%20Let's%20also%20figure%20your%20table%20spans%20columns%20A%20thru%20Z.%3C%2FP%3E%3CP%3EIn%20%22Conditional%20Formatting%22%20(Home%20ribbon)%2C%20add%20a%20New%20Rule%20and%20choose%20%22Use%20a%20formula%20to%20determine%20which%20cells%20to%20format%22.%20For%20the%20formula%2C%20use%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTODAY()-%24A2%20%26gt%3B%20%7Binsert%20%23%20days%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAnd%20apply%20the%20rule%20to%20the%20entire%20table.%20Note%20the%20'%24'%20MUST%20be%20in%20front%20of%20the%20column%20with%20the%20dates%20and%20there%20must%20be%20NO%20%24%20in%20front%20of%20the%20row%20number%20(2%20in%20this%20example).%20Then%20set%20the%20formatting%20of%20the%20conditional%20format%20as%20you%20like%20(e.g.%2C%20pick%20a%20%22fill%22%20color).%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi - 

 

Like many posts, relatively new to excel, though I have some working experience. I'm trying to build a page that would highlight an entire row a certain color if a date is too far removed from a =TODAY() cell.

 

Its basically a way to ensure that tasks do not get too out of hand and I could visually see if something was done 2 weeks ago, and it should have been done within a week, then I would have a visual trigger to show that that task needs attention.

 

I built my excel sheet so that tasks would need to be checked off in a linear fashion along the same row across multiple columns. So long as a date is input into the next column and the last date input isn't too far removed from the =TODAY() cell, a row would remain white. However, once it's been a certain length of time, then that row would highlight red.

 

I know this is complicated, but any insight would be appreciated.

2 Replies

@ryn8985 

Let's say your Date column is Column A and the data begin in row 2. Let's also figure your table spans columns A thru Z.

In "Conditional Formatting" (Home ribbon), add a New Rule and choose "Use a formula to determine which cells to format". For the formula, use:

 

=TODAY()-$A2 > {insert # days}

 

And apply the rule to the entire table (A2:Znn, where nn = last row with data). Note the '$' MUST be in front of the column with the dates and there must be NO $ in front of the row number (2 in this example). Then set the formatting of the conditional format as you like (e.g., pick a "fill" color).

... and if you really want to get fancy, instead of specifying the number of days in the formula, instead point to a cell somewhere else (better yet, a named range), and the highlights will change when the value in that cell changes:

 

= TODAY() - $A2 > TimeLimit

Where you've named a cell "TimeLimit". Changing the value of that cell from 7 to 14 will change from highlighting anything older than one week to anything older than two weeks, etc.