• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting where a date is not properly entered

glennpc

Member
We have users that record issues into a system, and they need to enter the date the issue is created and then a due date-- the due date is supposed to be 3 business days from the created date. (Examples: created date is 10/6/2015, so the correct due date to enter would be 10/9/2015. If the created date was 10/9/2015, the correct due date would be 10/15/2015 because it would not count the 10th and 11th because those are weekend days, and it would also not count the 12th because that is a holiday.)

I have a conditional formatting rule set on the Due Date values using this formula:

=NETWORKDAYS(F4, G4, $L$3:$L$4)<>3 (my holidays are in the L3 and L4 cells)

I'm getting unexpected results. Anybody know what I'm doing wrong?

I've attached my spreadsheet-- I color-coded out to the left what I think should be correct and what should be flagged by the conditional formatting.
 

Attachments

  • DateCheck.xlsx
    8.8 KB · Views: 2
L4 has 11 Nov not 11 Oct

Also the formulas in G6 & G7 are incorrect

The main problem is that your CF Formula is
=NETWORKDAYS(F4, G4, $L$3:$L$4)<>3

It should be:
=NETWORKDAYS(E4, F4, $L$3:$L$4)<>3
 
Last edited:
Thanks. I had the wrong columns. Also, in a subsequent test, I found that I need to use <>4, not <>3. It seems like the way workdays is counted is that it takes into account the starting day, and I wasn't doing that. So the NETWORKDAYS when the start is 10/6 and the end is 10/9 is 4, not 3.

Thanks for your help!
 
Hui-- if you create a macro that sets up the conditional formatting, should it work?

Attached is my workbook-- now it is macro enabled. Right now the macro has not been run-- instead, I use the conditional formatting that you helped me fix-- it works as it is supposed to right now.

But if you clear the conditional formatting from the sheet, and then run my macro (you can use the yellow button) -- it doesn't give the same results. I believe I'm using the correct rule in both cases. Any ideas why this doesn't work the same way?
 
You made a mistake in the macro:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NETWORKDAYS(E4, F4, $L$4:$L$5)<>4"

should be:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NETWORKDAYS(E4, F4, $L$3:$L$4)<>4"

You still have the wrong formulas in G6 & G7
 
Back
Top