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!

JamesR

Assuming that Modified29 is the System Modified date Column there is an incompatibility due to Today() being a Date Column and Modified being a Date and Time Column.

Use

Dateonly(modified29)

That should do the trick.

Katie Williams

Thanks for the help James, good tip for the future!  It didn't seem to fix my issue however.  I'm am now getting an #UNPARSEABLE error in that cell.

JamesR

You are missing an Closing Bracket for the OR function.

=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))

Try that

 

Katie Williams

Thank you so very much - it looks like that worked!

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))