Using date formula in conditional formatting based on working days

[stuburden]

Hi

I am trying to create conditional formatting in Excel 2007 to highlight a cell based on its value in comparison to today's date.

I have a cell (B1) which has a value of TODAY().

I want to express conditional formatting for a range of cells (B4:J90) which all have a date value in them.

I want to apply conditional formatting as follows:
*Date is less than today (B1) = Red
*Date is between today and today + 3 (working days) = Amber
*Date is greater than 3 working days = Green

The first rule is easy. I have used "Format only cells that contain" and a "format only cells with" Cell value.....less than......=$B$1.

I am struggling to create a formula for the last two rules I want to apply, as I only want to count working days (i.e. exclude weekends).

Please can anyone assist?

 

[Joe4]

Welcome to the Board!

The key to doing what you want is the NETWORKDAYS function (see: NETWORKDAYS - Excel - Office.com).
You should be able to use that to accomplish your task.