Forum Discussion
Conditional format dates
Hi,
Is it possible to apply a conditional format to a cell.
If column I states Yes then highlight red if the date is 5 workings days old and over
If column I states No the highlight red if the date is 10 working days old and over
If column I states NA or is blank then to not highlight
Please explain in detail what you mean by:
the date (which date?) is 5 or 10 working days old or over - compared to today? or to a date in another column?
Hello,
Apologises, the conditional format should be applied to colum J. The dates are entered in this column and I would like it to highlight when the date is either 5 or 10 working in the past
Thank you.
Select the cells in column J that you want to format conditionally.
The active cell in the selection should be in its top row, i.e. J2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND(J2<>"", OR(AND(I2="yes", NETWORKDAYS(J2, TODAY())>=5), AND(I2="no", NETWORKDAYS(J2, TODAY())>=10)))
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.
