Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

nested if ifs

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Nested IF / IFS Function

Nested IF / IFS Function

ResolvedVersion 365

Simon has attended:
Excel Intermediate course
Excel Advanced course
Customer Service Excellence course

Nested IF / IFS Function

Hi - I'm trying to compile some KPI reporting but struggling to get the right combination of nested IF functions (and also tried IFS to no avail).

I need to show:
If an order is sent to the warehouse before midday on any given day, the pack slip must be returned on the same day to "PASS", otherwise "FAIL".
If an order is sent after midday on any given day, the pack slip must be returned by the following day to "PASS, otherwise "FAIL".

So far I've got the following:

Column C = sent date
Column D = before midday (Y/N)
Column E = return date

=IF(D2="Y",IF((NETWORKDAYS(E2,C2)-1)=0,"PASS","FAIL"),IF((NETWORKDAYS(E2,C2))=1,"PASS","FAIL"))

Above formula returns "PASS" if dates are equal regardless of whether D = "Y" or "N". What is missing?

Thanks in advance for your feedback,
Simon

RE: Nested IF / IFS Function

Hi Simon,

Thank you for the forum question.

Try:

=IF(AND(D2="Y",(NETWORKDAYS(C2,E2)-1)=0),"PASS",IF(AND((NETWORKDAYS(C2,E2)-1)=1,D2="N"),"PASS","FAIL"))

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Excel tip:

Creating custom lists

In Excel if you type in January in a cell, you can then copy this cell to replicate Febraury, MArch, April etc.

This list has come from Tools- options and Custom lists.

Therefore to save time and create your own list you can click on New (in Tools and custom list tab) and type out the lsit that you want copied quickly.

All you have to do is then type in the 1st word and you will be able to copy the rest of the list quickly.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.