Need help with nesting multiple IF functions

[source]

Hi,

New to the site, thanks for your input!

I am trying to create an approval tracking sheet to display if a file is overdue or awaiting completion at various stages, something along the lines of this...

C4 is the date sent to client (with an expect turnaround of 2 workdays) and D4 is the date returned from client.

I think the error lies in the first statement of when a file is not yet returned (blank cell)

=IF(D4="","waiting for client"),IF(D4>WORKDAY(C4,2),"overdue from client"))

Thanks.

[Pete_UK]

You don't need that close-bracket in the middle after client"

Hope this helps.

Pete

[source]

whoops, I knew that, just a typo.

The problem is that without a value in D4, it's still not returning an "overdue from client" when there is 2 workdays after C4.

I am not sure how to use it properly, but perhaps the DATEDIF function?

[Pete_UK]

I don't understand - if D4 is empty then you are "waiting for client", aren't you?

You should have an action-if-false at the end of your second IF - maybe something like this:

=IF(D4="","waiting for client",IF(D4>WORKDAY(C4,2),"overdue from client","okay"))

Hope this helps.

Pete

[source]

yes, I am still waiting for client, but want to indicate that I am waiting for client AND they are overdue.

I know the solution must be with DATEDIF or TODAY functions, just not sure how to go about it.

I hear that the WORKDAY function kind of messes things up.

[protonLeah]

maybe:

Please Login or Register  to view this content.

[Pete_UK]

Try this:

=IF(D4="",IF(TODAY()>WORKDAY(C4,2),"overdue from client","waiting for client"),IF(D4>WORKDAY(C4,2),"overdue from client","okay"))

Hope this helps.

Pete

[source]

yes!

That's it.

Thanks!

I am now going to make it more comlex and try to throw a day count on how many days the file is in progess, how many days they are overdue. Will post my results.

[source]

Wheee, this is fun
I have managed to get the count in doing this,
=IF(ISBLANK(D4), IF(TODAY()>WORKDAY(C4,2),"Overdue-"&DATEDIF(WORKDAY(C4,2),TODAY(),"d"),"In Progress for "&DATEDIF(C4,TODAY(),"d")), IF(D4>WORKDAY(C4,2),"Returned- late","Returned"))

but have tried, but cannot seem include the DATEDIF function to do a day count of how many days late.

[newdoverman]

I worked this out and maybe it will work for you. If the client hasn't responded within 2 days, "Waiting for client". If the client hasn't responded after 2 days "Overdue from client". If D4 is has a date within 2 days of C4 "OK" otherwise "Overdue from client". I think that this covers all the possibilities.

Please Login or Register  to view this content.

[Pete_UK]

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete