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 dat...
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.