Home

Nesting "IF" with "IF AND" functions with 4 values

%3CLINGO-SUB%20id%3D%22lingo-sub-712105%22%20slang%3D%22en-US%22%3ENesting%20%22IF%22%20with%20%22IF%20AND%22%20functions%20with%204%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712105%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20project%20management%20excel%20sheet%2C%20I'd%20like%20it%20to%20auto-populate%20whether%20a%20task%20is%20%22Complete%22%2C%20%22Overdue%22%2C%20%22In%20Progress%22%2C%20or%20%22Not%20Started%22%20based%20on%20three%20cells%20and%20the%20dates%20they%20do%20or%20do%20not%20contain.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20have%20but%20I'm%20getting%20an%20error%20on%20it%3C%2FP%3E%3CP%3E%3DIF(OR(D10%3CTODAY%3E%3C%2FTODAY%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20D10%20is%20greater%20than%20today%20or%20blank%2C%20it%20should%20return%20%22Not%20Started%22%3C%2FP%3E%3CP%3EIf%20F10%20has%20a%20date%2C%20it%20should%20return%20%22Completed%22%3C%2FP%3E%3CP%3EIf%20F10%20is%20blank%20and%20E10%20is%20greater%20than%20TODAY%2C%20it%20should%20return%20%22Overdue%22%3C%2FP%3E%3CP%3EIf%20F10%20is%20blank%20and%20E10%20is%20less%20than%20TODAY%2C%20it%20should%20return%20%22In%20Progress%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20but%20one%20function%20is%20working.%20If%20D10%20is%20greater%20than%20today%20it%20is%20returning%20%22In%20Progress%22%20when%20it%20should%20return%20%22Not%20Started%22.%26nbsp%3B%20I'm%20thinking%20my%20statements%20overlap%20or%20contradict%20somewhere%20but%20I'm%20stuck.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-712105%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712161%22%20slang%3D%22en-US%22%3ERe%3A%20Nesting%20%22IF%22%20with%20%22IF%20AND%22%20functions%20with%204%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712161%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364097%22%20target%3D%22_blank%22%3E%40KCharron%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(%20(%24D10%26gt%3BTODAY())%2BISBLANK(%24D10)%2C%22Not%20Started%22%2CIF(%24F10%26gt%3B100%2C%22Completed%22%2CIF(%24E10%26gt%3BTODAY()%2C%22Overdue%22%2C%22In%20Progress%22)))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713614%22%20slang%3D%22en-US%22%3ERe%3A%20Nesting%20%22IF%22%20with%20%22IF%20AND%22%20functions%20with%204%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you!%26nbsp%3B%20It%20solved%20my%20%22Not%20Started%22%20issue%20but%20wiped%20out%20my%20%22Overdue%22%20rule.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20all%20seems%20to%20work%20if%20I%20combine%20your%20solution%20with%20mine%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(%20(%24D4%26gt%3BTODAY())%2BISBLANK(%24D4)%2C%22Not%20Started%22%2CIF(NOT(ISBLANK(F4))%2C%22Complete%22%2CIF(AND(F4%3D%22%22%2CE4%3CTODAY%3E%3C%2FTODAY%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713739%22%20slang%3D%22en-US%22%3ERe%3A%20Nesting%20%22IF%22%20with%20%22IF%20AND%22%20functions%20with%204%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713739%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DIF(OR(D10%26gt%3BTODAY()%2CD10%3D%E2%80%9C%E2%80%9D)%2C%3CBR%20%2F%3E%E2%80%9CNot%20Started%E2%80%9D%2C%3CBR%20%2F%3EIF(F10%26gt%3B0%2C%E2%80%9DCompleted%E2%80%9D%2C%3CBR%20%2F%3EIF(AND(F10%3D%E2%80%9C%E2%80%9D%2CE10%26gt%3BTODAY())%2C%3CBR%20%2F%3E%E2%80%9COverdue%E2%80%9D%2C%E2%80%9DIn%20Progress%E2%80%9D)))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713848%22%20slang%3D%22en-US%22%3ERe%3A%20Nesting%20%22IF%22%20with%20%22IF%20AND%22%20functions%20with%204%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364097%22%20target%3D%22_blank%22%3E%40KCharron%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20formula%20works%20-%20please%20check%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E
KCharron
New Contributor

In my project management excel sheet, I'd like it to auto-populate whether a task is "Complete", "Overdue", "In Progress", or "Not Started" based on three cells and the dates they do or do not contain.

 

Here is what I have but I'm getting an error on it

=IF(OR(D10<TODAY(),D10=""),"Not Started",IF(NOT(ISBLANK(F10)),"Complete",IF(AND(F10="",E10<TODAY()),"Overdue","In Progress")))

 

If D10 is greater than today or blank, it should return "Not Started"

If F10 has a date, it should return "Completed"

If F10 is blank and E10 is greater than TODAY, it should return "Overdue"

If F10 is blank and E10 is less than TODAY, it should return "In Progress"

 

All but one function is working. If D10 is greater than today it is returning "In Progress" when it should return "Not Started".  I'm thinking my statements overlap or contradict somewhere but I'm stuck.

4 Replies

@KCharron 

 

It could be

=IF( ($D10>TODAY())+ISBLANK($D10),"Not Started",IF($F10>100,"Completed",IF($E10>TODAY(),"Overdue","In Progress")))

@Sergei Baklan thank you!  It solved my "Not Started" issue but wiped out my "Overdue" rule.

 

It all seems to work if I combine your solution with mine

=IF( ($D4>TODAY())+ISBLANK($D4),"Not Started",IF(NOT(ISBLANK(F4)),"Complete",IF(AND(F4="",E4<TODAY()),"Overdue","In Progress")))

Try this:
=IF(OR(D10>TODAY(),D10=“”),
“Not Started”,
IF(F10>0,”Completed”,
IF(AND(F10=“”,E10>TODAY()),
“Overdue”,”In Progress”)))

@KCharron 

 

IMHO, formula works - please check attached

Related Conversations