IF statement with AND/OR

Katie Williams

I am trying to create a formula that calculates Days Overdue based on varying approval levels.  I need it to calculate if Direct Manager Approval is "Submitted" and Hiring Manager/Director Approval is blank OR if Direct Manager Approval is "Approved" and Hiring Manager/Director Approval is "Submitted".  Here is what I came up with but I'm getting an error message "Incorrect Argument". 

=IF(AND([Direct Manager Approval]29 = "Submitted", [Hiring Manager/Director Approval]29 = ""), TODAY() - Modified29, IF(OR([Direct Manager Approval]29 = "Approved", [Hiring Manager/Director Approval]29 = "Submitted", TODAY() - Modified29, 0)))

Any help would be appreciated!

Robert S.

Hello Katie,

 

Thanks for the question. If I understand your post correctly, this formula will still not act exactly how you're looking for it to. From what I understand, you are wanting the calculation of TODAY() - Modified29 to run only if one of the following conditions are met:

• [Direct Manager Approval]29 is "Submitted" AND [Hiring Manager/Director Approval]29 is blank
• [Direct Manager Approval]29 is "Approved" AND [Hiring Manager/Director Approval]29 is "Submitted"

Currently with this formula, these are the conditions that will cause the calculation will happen:

• [Direct Manager Approval]29 is "Submitted" AND [Hiring Manager/Director Approval]29 is blank OR "Submitted"
• [Direct Manager Approval]29 is "Approved" AND [Hiring Manager/Director Approval]29 is anything including blank
• [Direct Manager Approval]29 is blank AND [Hiring Manager/Director Approval]29 is "Submitted"

If this is how you'd like it to work, then you're all set. However if my understanding of how you'd like it to work is correct, you can make this happen by changing the OR function to an AND function. Here's how that would look:

 

=IF(AND([Direct Manager Approval]29 = "Submitted", [Hiring Manager/Director Approval]29 = ""), TODAY() - Modified29, IF(AND([Direct Manager Approval]29 = "Approved", [Hiring Manager/Director Approval]29 = "Submitted"), TODAY() - Modified29, 0))