PDA

View Full Version : Calculating Difference of Workdays



llldebaserll
05-07-2014, 11:13 AM
Hi Folks,

There must be a simple solution to my problem but I can't seem to find it.

I'm trying to determine if a date is more than 3 workdays before today.

Below, I'm trying to mark any cell in the SortCells range as red if it is more than 3 days ago. I've found the NetworkDays.intl funtion deal but I'm certain I'm using it wrong. The Yellow and Green codes are simple and work fine but I can't get this more complicated workday scenario to work out.


For Each SortCell In SortCells
' Red
If NetworkDays.intl(SortCell.Value, Date, 1) > 3 Then SortCell.Interior.Color = 255
' Yellow
If SortCell.Value = Date Then SortCell.Interior.Color = 65535
' Green
If SortCell.Value = Date + 1 Then SortCell.Interior.Color = 5296274
Next SortCell

Any help would be greately appreciated!

Bob Phillips
05-07-2014, 11:47 AM
Just select the date range and use conditional formatting with a formula

=D2<WORKDAY(TODAY(),-3)

assuming your dates start in D2