Hi! I am hoping someone may be able to offer some advice on a date issue that has me tearing my hair out!
I have an excel spread sheet where:

A = date complaint was received
B = date complaint was responded to
C = the number of days to response,
Another sheet logs bank holidays.

I have used =NETWORKDAYS(A1,B1,anothersheet1:2) to return the number of working days for response and then conditional formatting shows if this is over the 10 working day response target.

All works really well until B is blank. If B is blank I need to determine if a response was due at the end of my quarter 01/04/2012 so:
IF B is blank and A1 to 01/04/2012 is less than 10 network days excluding bank holidays then excel returns “Not Due”
If B is blank and A1 to 01/04/2012 is more than 10 network days excluding bank holidays then excel returns “overdue”
I just seem to be going around in circles – any help would be greatly appreciated!
Thanks, Katie

Can you post a sample workbook so whomever is helping will have something to tinker with for you?

you could add a IF in front
IF( B1 <>"", NETWORKDAYS(A1,B1,anothersheet1:2), IF( NETWORKDAYS(A1,"01/04/2012",anothersheet1:2) <=10 "Not Due", "overdue" ))

That worked a treat etaf - thank you so so much youre a life saver! Its easy when you know how katie

Hi Katie,
Don't forget to mark your thread as solved and click the * under etaf's post and give etaf some credit for helping

Hi Guys,

Sorry to re bother, just wondering if you know of a way to work out a solution where i have 3 dates to consider in my formula?

I have been using the IF( NETWORKDAYS(A1,"01/04/2012",anothersheet1:2) <=10 "Not Due formula which works so well, but have now been asked to incorporate a holiding response which would mean:

IF(networkdays(A1,holding1,anothersheet1:2)<=10 AND holding1,01/04/2013,anothersheet1:2 <=10 then it is not due,

i swear this issue is going to be the end of me!

Any help greatly appreciated!

Thanks again,
Katie

you can do what you said

IF(networkdays(A1,holding1,anothersheet1:2)<=10 AND holding1,01/04/2013,anothersheet1:2 <=10 then it is not due,
IF ( AND ( networkdays(A1,holding1,anothersheet1:2)<=10 , networkdays( holding1,01/04/2013,anothersheet1:2) <=10 )

not sure what holding1 is

BUT if you want to do an AND
its

AND( test_for_true, test_for_true , etc )

where holding 1 is a cell with another date in it? call the cell D1? it wont let me use 3 dates in a Networkdays argument, i ideally need:

IF(AND(NETWORKDAYS(A1,D1,ANOTHERSHEET1:2)<=10,NETWORDAYS(D1,"01/04/2013",ANOTHERSHEET1:2)<=10),"NOT DUE") ??

The issue is the and i need the forumula to take into account the date the complait was recieved to a holding response was less than 10 days, and the holding response to 01/04/13 was less than 10 days then a response isnt due - its a bloomin nightmare!

the AND should work
as
=NETWORKDAYS(A1,D1,ANOTHERSHEET1:2)<=10

should be true
AND
NETWORDAYS(D1,"01/04/2013",ANOTHERSHEET1:2)<=10
should be true

to get the IF to return
"NOT DUE"

if either one is false it will return

so they must both be true

have you tried just putting the 2 formulas into separate cells to see what you get true or false as a test

=NETWORKDAYS(A1,D1,ANOTHERSHEET1:2)<=10
=NETWORDAYS(D1,"01/04/2013",ANOTHERSHEET1:2)<=10

in case the logic of those two formulas are not producing what you actually think they should

If i use the following i get a True result:

IF(NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10,NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10,"NOT DUE")

what i cant get is the argument to display "not due" or to be able to add an otherwise "overdue" , it just keeps saying i have too many arguments which is why i feel like i need an IF(AND(Networkdays( it just isnt a thing! Sorry to be annoying with this one - i always though i was quite good with excel!

you are evaluting just this part of the IF
NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10
then you have a comma
so if that first test bit is TRUE
it will then evaluate the true condition which is now
NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10
because of the comma - thats the syntax of the IF function
so thats the bit that you are seeing as true or false
if that was false you would get
"NOT DUE"
as thats how the IF has been set out by you
IF(test , do if test is True , do if test is False)

you do need the AND

NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10
AND
NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10

becomes

AND (NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10 , NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10)

then the IF
IF( test, true, false)

IF( AND (NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10 , NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10) , "NOT DUE" , "OVERDUE")

if both networkdays are true then you will get not due
if either one of the networkdays is false you get overdue

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1