Forum Discussion
IF function based on dates in cell
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
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 > TimeLimitWhere 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.
