Counting days issue

Monitoring Matrix.xlsx

Hi there

I need formula help. In column E I have a deadline date and in column F is the actual date the work was done. Column G calculates the difference between the dates to give whether in or out of timescales and red or green is then applied with conditional formatting. However, the issue is that all dates are counted therefore always one to0 many which impacts on the number in column G. Is there a way of adding to the formula in G which is =NETWORKDAYS(E5,F5,holidays) to discount one day? It is made more challenging by the fact that some deadlines are reached before deadline and others are not. As you can see, entries in F that are the same as in E are coming up with 1 not 0 and then flag red.

I hope you understand what I mean and can help!

Thanks

If you just want to universally decrease the result by one....

=NETWORKDAYS(E5, F5, holidays) -1

Hi
Adding -1 gives me #########

Originally Posted by maddy1980

Hi
Adding -1 gives me #########

That's because it's trying to format as a date and it doesn't like negative numbers. Change the format to "number" or "general" (ie, not "date") and it will be fine.

Originally Posted by Richard Buttrey

Hi, and welcome to the forum.

How about G5

Formula:

=(NETWORKDAYS(E5,F5,holidays))-IF(E5<=F5,1,-1)

Oh wow my hero it works! How good are you?!!! I shall seek you out in the future if I need help!

Ooh there's another part to my problem I almost forgot about. How do I ensure column G is blank until an entry is made in column F? Do I add to =(NETWORKDAYS(E5,F5,holidays))-IF(E5<=F5,1,-1)?

Many thanks in advance of your wisdom!

Originally Posted by ChemistB

One possibility

=IF(ISNUMBER(F5),NETWORKDAYS(E5,F5,holidays)-1,"")

This changes the number though allows a blank cell. I can't use -1. This was solved further up with =(NETWORKDAYS(E5,F5,holidays))-IF(E5<=F5,1,-1). If this can be incorporated with your formula maybe but I don't know how.

Thanks

I've cracked it: =IF(F5<=0,"",(NETWORKDAYS(E5,F5,holidays))-IF(E5<=F5,1,-1)). I will use the reformat of the red conditional formatting suggested by ChemistB.

Thanks

And I just used the blanks option in conditional formatting to stop blank cells going red. Thanks one and all for your help. Much appreciated.