In the Format values where this formula is true, enter the formula: TODAY()-C190 This formula checks to see if the dates in cells C1 to C4 are greater than 90 days past.

How to Use Custom Conditional Formatting Rules for Dates in Excel.

Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.

Ryan Perian is a certified IT specialist who holds numerous IT certifications and has 12 years experience working in the IT industry support and management positions.

Adding conditional formatting to a cell in Excel allows you to apply different formatting options, such as color, when the data in that cell meets the conditions that you have set.

In the case of dates, the pre-set options simplify the process of checking your data for dates close to the current date such as yesterday, tomorrow, last week, or next month.

If you want to check for dates that fall outside of the listed options, however, customize the conditional formatting by adding your own formula using one or more of Excels date functions.

Customize conditional formatting using formulas by setting a new rule that Excel follows when evaluating the data in a cell.

Excel applies conditional formatting in top-to-bottom order as they appear in the Conditional Formatting Rules Manager dialog box.

Even though several rules may apply to some cells, the first rule that meets the condition is applied to the cells.

This is the range to which the conditional formatting rules will be applied.

Select the Fill tab to see the background fill color options.

Set the font color.

Select OK twice to close the dialog boxes and return to the worksheet.

The background color of cells C1 to C4 changes to the fill color chosen, even though there are no data in the cells.

Rather than repeat all the steps above to add the next two rules, use the Manage Rules option to add the additional rules all at once.

Select Home Conditional Formatting Manage Rules to open the Conditional Formatting Rules Manager dialog box.

Select New Rule. Select Use a formula to determine which cells to format.

In the Format values where this formula is true text box, enter the formula: TODAY()-C160 This formula checks to see if the dates in cells C1 to C4 are greater than 60 days past.

Select OK twice to close the dialog box and return to the Conditional Formatting Rules Manager dialog box.

Auto Format Cell Excel For Mac