where is conditional formating in excel for mac

Where Is Conditional Formating In Excel Download The Demonstration

The expected date is August 30, 2017--you dont see that in the sheet, but the expression accounts for it.

Here are two ways to apply a complex conditional format to accomplish a tricky goal.

They try, but they simply cant get all the pieces to work together correctly.

Sarina inherited a workbook that stores a contract date with an expected fulfillment date thats always 60 days out.

When the actual fulfillment date falls outside of a three-day window (either way), she wants a conditional format rule to highlight the fulfillment date.

Youll also see two solutions: one that satisfies the requirements and a second thats more flexible.

To be fair to Sarina, her requirements are more complex than the solution Im sharing, but the concept is the same.

Im using Excel 2016 (desktop) on a Windows 10 64-bit system, but this solution will work in earlier versions.

Where Is Conditional Formating In Excel Download The Demonstration

For your convenience, you can download the demonstration.xlsx and.xls files.

Excels browser version supports existing conditional formatting, but you cant apply a formulaic rule in the browser.

When the actual fulfillment date is outside the theree-day - window based on the expected date, she wants the actual date highlighted.

The expressions in columns E and F return the - dates for each contract date; you dont need them, but they help illustrate whats happening.

Figure A A conditional formatting rule highlights dates outside the expected window.

Cutting corners often works, but it can have unexpected consequences that are hard to troubleshoot down the road.

Theres a reason for both the 60-day and - window, and either or both could change.

To implement the formatting rule, do the following: Select D3:D6, the range of actual dates.

Click the Home tab.

Choose New Rule from the Conditional Formatting dropdown (in the Styles group).

For this example, click the Fill tab, choose red, and click OK.

By comparing the results to the dates in columns E and F, its easy to see why the rule works as it does.

Remember, these two columns arent part of the solution, but they do help clarify whats going on.

For example, August 26 falls before the expected window of 60-3; September 25 falls after the expected window of 603.

The other two dates, September 15 and September 30, fall within the - span of the expected date.

This rule works in large part due to the OR() function, which evaluates multiple conditions using the following syntax: OR(cond1,cond2,.).

Where Is Conditional Formating In Excel For Mac