Complex IF, AND, OR and Networkdays

[craig80]

hello All,

I have become very dandy at a few functions of late. However one that always eludes me is networkdays. I just can't get it. I realised after lots of trial and error, you cant incorroprate ranges that relate to more than one value.

The below is a formula that sits in a larger IF statement. I am trying to assess the record against various criteria, but just can't seem to get what i want from it.

IF(AND(OR(DueDate=today-1,DueDate=today-2),ActualDate=""),"Admin Must be completed",....

I need to incorporate network days into the "Today-x" i have tried all sorts of variations.

...and(or(duedate=today-1, (networkdays(duedate, today, holidays)<>1)),(duedate=today-1, (networkdays(duedate, today, holidays)<>2))....

I would like to see this result returned for two network days after the due date. (which is always a network day).

If anyone can see it from a differnt approach, I am all ears... or if someone can help on the formulas above... even better.

Thanks you.

Craig

 

[GlennUK]

Shouldn't you be comparing DueDate to some date offset from today using the WORKDAY function?

 

[jasonb75]

Maybe something on the lines of

=lookup(today()-duedate,{0,2,x},{"","Admin must be completed","Some other message"})

 

[craig80]

Hi Guys,

thanks for getting back to me. In the end i went for the Workday. I used it against a smaller data set, then factored in ranges.

I used this because i didn't want my sheet calculating work day, 1000's of times.

New formula looks something like... (i have left it on work machine!)

IF(AND(OR(DueDate=Minus1,DueDate=Minus1),ActualDate=""),"Admin Must be completed"

ranges.

=workday(today(),-1,Holidays)
=workday(today(),-2,Holidays)
etc...

Thanks for the lead folks.

Craig