Trying to use IF and OR in combination for given scenerio

Options

Hi,

I have a requirement were I am checking values in 2 column using IF and OR and basis that the formula should return date after pre-defined days. I tried following statement but is it giving #INVALID ARGUMENT error.

=IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), DATE([Start Date]@row + 17), IF(OR(Level@row = 2, [New/Existing]@row = "Existing Request"), DATE([Start Date]@row + 30)))

Any help on this?

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Prajna Jain

    The date function is being used incorrectly and is not needed for this formula.

    Try the below function where i removed the DATE() function.

    =IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), [Start Date]@row + 17, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Request"), [Start Date]@row + 30))

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Prajna Jain

    The date function is being used incorrectly and is not needed for this formula.

    Try the below function where i removed the DATE() function.

    =IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), [Start Date]@row + 17, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Request"), [Start Date]@row + 30))

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    @Leibel S Thanks a lot it worked

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    I am tried to make the above argument more generic and added column reference instead of manually adding number of days.

    =IF(OR(Level@row = 1, [New/Existing]@row = "New Document Request"), DATE([Start Date]@row + Duration@row, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Document Request"), DATE([Start Date]@row + Duration@row))))

    But it is giving #INVALID DATA TYPE error.

    Would appreciate quick help on this.

    Thanks in anticipation.

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @Prajna Jain

    you added the date function in again... Remove that and it should work.

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    @Leibel S wow its is working...thanks.

    one more thing that I found problematic is that if the one of the reference columns is blank it is throwing #INVALID OPERATION error.

    Thanks again for helping!

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @Prajna Jain

    Wrap the entire function in an IFERROR()

    =IFERROR(IF(OR(Level@row = 1, [New/Existing]@row = "New Document Request"), [Start Date]@row + Duration@row, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Document Request"), [Start Date]@row + Duration@row)),"")

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    @Leibel S that's great,

    I have one more query to bother may be how I can modify/frame this formula so that it does calculate ONLY the working days?

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    I tried adding WORKDAY function, but giving #INVALID ARGUMENT error:

    =IFERROR(IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), [Start Date]@row + (WORKDAY([Start Date]@row, Duration@row)), IF(OR(Level@row = 1, [New/Existing]@row = "Existing Request"), [Start Date]@row + (WORKDAY([Start Date]@row, Duration@row)))))

    Any immediate help on this?

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    See below corrected syntax

    =IFERROR(IF(OR(Level@row = 1, [New/Existing]@row = "New Document Request"), WORKDAY([Start Date]@row,Duration@row), IF(OR(Level@row = 2, [New/Existing]@row = "Existing Document Request"), WORKDAY([Start Date]@row,Duration@row))),"")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!