Home

Need help with nesting If formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-394852%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20nesting%20If%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394852%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20formula%20that%20I%20am%20trying%20to%20combine%20another%20IF%20statement%20too%2C%20but%20I%20keep%20receiving%20errors.%26nbsp%3B%20Here%20is%20my%20statement%20that%20is%20working%20without%20the%20extra%20IF%3A%3C%2FP%3E%3CP%3E%3DIF(AND(I4%26lt%3B%26gt%3B%22%22%2CD4%3D%22Service%20Completed%22)%2C%22%22%2CTODAY()-I4)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BThe%20problem%20is%20that%20this%20formula%20also%20calculates%20when%20I4%20is%20blank%20and%20I%20only%20want%20it%20to%20calculate%20Today-cell%20if%20there%20is%20data%20in%20the%20cell.%20So%20I%20tried%20to%20put%20another%20IF%20statement%2C%20but%20it%20give%20me%20an%20error.%26nbsp%3B%20Here%20is%20what%20I%20tried%3A%3C%2FP%3E%3CP%3E%3DIF(AND(I4%26lt%3B%26gt%3B%22%22%2CD4%3D%22Service%20Completed%22)%2C%22%22%2CTODAY()-I4%2CIF(I4%3D%22%22%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-394852%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-395115%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20nesting%20If%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-395115%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F135467%22%20target%3D%22_blank%22%3E%40Cara%20Hunter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20this%20perform%20the%20calculation%20you%20require%3F%3C%2FP%3E%3CP%3E%3DIF(%20(Date%26lt%3B%26gt%3B%22%22)%20*%20(Status%26lt%3B%26gt%3B%22Service%20Completed%22)%2C%20TODAY()-Date%2C%20%22%22%20)%3C%2FP%3E%3CP%3EEssentially%2C%20it%20calculates%20days%20overdue%20of%20an%20open%20task%20provided%20a%20termination%20date%20%3CSPAN%3Eexists%3C%2FSPAN%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Cara Hunter
New Contributor

I have a formula that I am trying to combine another IF statement too, but I keep receiving errors.  Here is my statement that is working without the extra IF:

=IF(AND(I4<>"",D4="Service Completed"),"",TODAY()-I4)

 

 The problem is that this formula also calculates when I4 is blank and I only want it to calculate Today-cell if there is data in the cell. So I tried to put another IF statement, but it give me an error.  Here is what I tried:

=IF(AND(I4<>"",D4="Service Completed"),"",TODAY()-I4,IF(I4="",""))

 

1 Reply

@Cara Hunter 

Does this perform the calculation you require?

=IF( (Date<>"") * (Status<>"Service Completed"), TODAY()-Date, "" )

Essentially, it calculates days overdue of an open task provided a termination date exists.