Thread: Conditional Formatting Date Before Expiry Date

Hi All-

I'm working on a spreadsheet in Excel 2007, and im having an issue.

Ok here goes, i have a =now() in cell F1, and an anticipated date of completion in column G, i need column G to hightlight in red the day before it is due. For example today (F1) is 11/21/11, i need everything in column G that is 11/20/11 to highlight in red and so on when the date changes daily.

Hope i made sense and you can help!

Thanks Canada

Change formula in F1 to =TODAY() so that time is not involved.

Assuming column G is only dates (no times), then select the column G from G2 down, go to Home|Conditional Formatting|New Rule

Select Format Only Cells That Contain from rule types section

Select Cell Value >> Equal To and enter =$F$1-1


Click Format and choose from Fill tab.

Highlight column G

Conditional formatting > New Rule > Use a formula to determine which cells to format

Enter =G1=TODAY()-1

if you want cells highlighted ONLY the day before they're due, or

=G1>=TODAY()-1

if you want cells to remain highlighted once they've passed their due date.

Highlight column G, go to conditional formatting, highlight cell rules, A date occurring, choose tomorrow (or yesterday not sure which you want?). This should do it.

Thank you that worked!