Skip to content
  • Try Smartsheet

    • Just for fun

    Just for fun

    A place to connect with your fellow Can-Doers for casual conversation, random thoughts, or networking.

    IF statement with AND/OR

    edited 11/30/-1 in Just for fun

    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!

    Insightful Vote Up Awesome · Share on FacebookShare on Twitter

    Welcome!

    It looks like you're new here. Sign in or register to get started.
    Sign In
    Try Smartsheet

    Comments

    • Employee
      edited 11/30/-1

      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.

      Insightful Vote Up Awesome · Share on FacebookShare on Twitter
    • edited 11/30/-1

      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.

      Insightful Vote Up Awesome · Share on FacebookShare on Twitter
    • Employee
      edited 11/30/-1

      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

       

      Insightful Vote Up Awesome · Share on FacebookShare on Twitter
    • edited 11/30/-1

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

      Insightful Vote Up Awesome · Share on FacebookShare on Twitter
    • edited 11/30/-1

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


      Insightful Vote Up Awesome · Share on FacebookShare on Twitter

    Leave a Comment

    Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.

    Comment As ...

    Welcome!

    It looks like you're new here. Sign in or register to get started.
    Sign In
    Try Smartsheet

    Quick Links

    Categories

    Trending in Just For Fun