IF statement with AND/OR
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!
Comments
-
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.
-
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.
-
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
-
Thank you so very much - it looks like that worked!
-
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))
-
Thanks Robert! Very much appreciate you taking the time to double check the formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 287 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!