Conditional Formatting Help

[ryandonovan22]

Hello,

I am trying to apply conditional formatting to a large range of cells, however I am stuck.

I need three rules
1. if the cell is blank then leave it white
2. if the date in the cell is less than 45 days ahead of today (I will format accordingly)
3. if the date in the cell is less than today (I will format accordingly)

Each cell is the reference for its own conditional formatting

Thanks.

 

[Joe4]

For Condition 2, are you only looking at future dates?
If so, then the Condition should look like (for a date in cell A1):

=AND(A1>0,A1>TODAY(),A1 < (TODAY()+45))

For Condition 3, it would just look like:

=AND(A1>0,A1 < TODAY())

Note: Since we are checking to make sure that the date is not blank in conditions 2 and 3, and your condition 1 is not doing any formatting at all, we can drop that condition (not needed at all).

To apply this on a large range of cells all at once, first select the entire range that you want to apply it to. Then write the Conditional Formatting formulas as they apply to the very FIRST cell in your range. Excel is smart enough to adjust for all the other cells.

 

[ryandonovan22]

For Condition 2, are you only looking at future dates?
If so, then the Condition should look like (for a date in cell A1):

=AND(A1>0,A1>TODAY(),A1 < (TODAY()+45))

For Condition 3, it would just look like:

=AND(A1>0,A1 < TODAY())

Note: Since we are checking to make sure that the date is not blank in conditions 2 and 3, and your condition 1 is not doing any formatting at all, we can drop that condition (not needed at all).

To apply this on a large range of cells all at once, first select the entire range that you want to apply it to. Then write the Conditional Formatting formulas as they apply to the very FIRST cell in your range. Excel is smart enough to adjust for all the other cells.

Thanks for your help, it is most appreciated.

Do you know if every time this spread sheet is opened excel will execute the formula based on the current day, or do I need to set it up to do this?

 

[Joe4]

TODAY() is a dynamic function. It will always return the current date, no matter what day it is.
So if you open it tomorrow, it will reflect tomorrow's date.

If you wanted a static date, you would need to hard-code it in there.