The expressions in columns E and F return the +/- dates for each contract date you don't need them, but they help illustrate what's happening. Translated into an expression, you end up with the following syntax: OR(actual(contract+60)+3įigure A shows this expression in use. The expected date is implicit-it doesn't exist in the sheet.
When the actual fulfillment date is outside the theree-day +/- window based on the expected date, she wants the actual date highlighted. Sarina has three dates: a date where counting begins, the contract date, and a date where counting might end, a 60-day expected fulfillment date. SEE: Build your Excel skills with these 10 power tips (TechRepublic download) Accommodating what you have Excel's browser version supports existing conditional formatting, but you can't apply a formulaic rule in the browser. For your convenience, you can download the demonstration. I'm using Excel 2016 (desktop) on a Windows 10 64-bit system, but this solution will work in earlier versions. To be fair to Sarina, her requirements are more complex than the solution I'm sharing, but the concept is the same.
You'll also see two solutions: one that satisfies the requirements and a second that's more flexible.
In this article, I'll show you how to use an OR() operator to evaluate the two ends of the three-day window and the 60-day conditions.
0 kommentar(er)
