Conditional Formatting for Variance - Forecasted and Actual Dates

Welcome to the forum.

To format your table of dates, you don't need the variance columns. Using your example file, do the following:
1. Select the range D5:AN74 (from the 'Actual Date 1' of 'Project 1' to 'Actual Date 19' of 'Project 70');
2. Click 'Conditional Formatting', 'New Rule', 'Use a formula to determine which cells to format';
3. Enter the formula below and choose red text as the format:

Formula:

Please Login or Register  to view this content.

4. Repeat with the formula below with green text as the format:

Formula:

Please Login or Register  to view this content.

Note that neither of these rules will format the text if the date is equal to the forecasted date. Change the > to >= for 'on or after the forecasted date' or the < to <= for 'on or before'.

You're welcome, glad to be of help.

The functions are:
AND
AND(one thing, second thing) - so the CF will only be applied if both are true
MOD
MOD(number, divisor) - the remainder left over when the number is divided by the divisor.

In this case, the number is the number of the column (A=1, B=2, etc). The divisor is 2. So even-numbered columns (B,D,F,etc) will have a remainder/modulus of 0 when divided by 2.

COLUMN
COLUMN() returns the column number. If you were to put in COLUMN(A1), you'd always get 1, COLUMN(B1) would always be 2, etc.
> or <
D5>C5 or D5<C5 just compare the dates. Dates in Excel are stored as numbers (1 = 1st Jan 1900), so a later date is a larger number.

The reason you can use D5 without trying to specify the whole range in the CF formula is that when you apply CF to a whole range, you just need to specify the top-left cell and Excel will automatically change the cell when it looks at the rest of the range. So D5>C5 will become (invisibly) E5>D5 when Excel is CF-ing E5, etc. If you click CF then 'Manage Rules', you'll see that there is a box labelled 'Applies to'. So if/when you add more rows/columns, if Excel doesn't automatically apply the same formatting (that depends how you insert the new rows/columns), then you can expand the range from D5:AN74 to (for example) D5:AP86 by using 'Manage Rules', instead of trying to (re)apply the CF manually to the new entries.

I hope that all makes sense. I encourage you to do some further research anyway as it's a great way to learn - just play around with a few formulae/functions and it's amazing how quickly you'll learn more. We're all still learning on here too .


If the previous answer takes care of your original question, please take a moment to mark the thread as 'Solved' so others know there's an answer here (instructions in my sig). Thanks.