IF AND OR Formula

Options

Hello, I'm want to add 6 months to "C Date" if "In Freq" value is "Semi-Annual" and add 1 year to the "C Date" if "In Freq" value is "Annual". I got an error "#Incorrect Argument Set", below is my formula. Please can you help? Thank you.

=IF(OR(AND([IN Freq]@row = "Semi-Annual"), IFERROR(DATE(YEAR([C Date]@row), MONTH([C Date]@row) + 6, DAY([C Date]@row)), DATE(YEAR([C Date]@row) + 1, MONTH([C Date]@row) - 6, DAY([C Date]@row), OR([IN Freq]@row = "Annual"), IFERROR(DATE(YEAR([C Date]@row), DATE(YEAR([C Date]@row) + 1, MONTH([C Date]@row), DAY([C Date]@row))))))))

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this IFERROR addition when [C Date] is blank. Also... The bold portion should be outputting if the [In Freq] is not "Semi-Annual".


    =IFERROR(IF([In Freq]@row = "Semi-Annual", IFERROR(DATE(YEAR([C Date]@row), MONTH([C Date]@row) + 6, DAY([C Date]@row)), DATE(YEAR([C Date]@row), MONTH([C Date]@row) - 6, DAY([C Date]@row))), DATE(YEAR([C Date]@row) + 1, MONTH([C Date]@row), DAY([C Date]@row))), "")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try this one...

    =IF([In Freq]@row = "Semi-Annual", IFERROR(DATE(YEAR([C Date]@row), MONTH([C Date]@row) + 6, DAY([C Date]@row)), DATE(YEAR(C Date]@row), MONTH([C Date]@row) - 6, DAY([C Date]@row))), DATE(YEAR(C Date]@row), MONTH([C Date]@row), DAY([C Date]@row)))

  • BonW
    BonW ✭✭
    Options

    Hello Paul, Thank you very much for response to my post.

    I received "#UNPARSEABLE". I might not stated my need. I would like to add 6 months to "C Date" if "IN Freq" value is "Semi-Annual" to "IN Date" column; and add 1 year to "C Date" if "IN Freq" value "Annual" to "IN Date".

    Thank you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    My apologies. I missed an opening square bracket for two of the column names.


    =IF([In Freq]@row = "Semi-Annual", IFERROR(DATE(YEAR([C Date]@row), MONTH([C Date]@row) + 6, DAY([C Date]@row)), DATE(YEAR([C Date]@row), MONTH([C Date]@row) - 6, DAY([C Date]@row))), DATE(YEAR([C Date]@row), MONTH([C Date]@row), DAY([C Date]@row)))

  • BonW
    BonW ✭✭
    Options

    Hello Paul,

    The formula work if there's value for the "C Date" and if the "C Date" is blank, I got an this error message "#INVALID DATA TYPE". I added "+ 1" in BOLD to handle adding one year if the "In Freq" with value "ANNUAL" . It's work and not sure if this is the right way or not. Below is the formula. Can you help?

    =IF([In Freq]@row = "Semi-Annual", IFERROR(DATE(YEAR([C Date]@row), MONTH([C Date]@row) + 6, DAY([C Date]@row)), DATE(YEAR([C Date]@row), MONTH([C Date]@row) - 6, DAY([C Date]@row))), DATE(YEAR([C Date]@row) + 1, MONTH([C Date]@row), DAY([C Date]@row)))

    Thank you again for your assistance greatly appreciate it!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I actually meant to include that +1. Apparently my fingers were not cooperating. The error though is expected fi there is no [C Date]. Did you want it to display a blank or use a different date?

  • BonW
    BonW ✭✭
    Options

    Thank you for your response.

    If "C Date" is blank then blank. I also like to add one year if the "In Freq" value is "Annual". the formula below only handle if "In Freq" value is "Semi-Annual".

    Thank you again.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this IFERROR addition when [C Date] is blank. Also... The bold portion should be outputting if the [In Freq] is not "Semi-Annual".


    =IFERROR(IF([In Freq]@row = "Semi-Annual", IFERROR(DATE(YEAR([C Date]@row), MONTH([C Date]@row) + 6, DAY([C Date]@row)), DATE(YEAR([C Date]@row), MONTH([C Date]@row) - 6, DAY([C Date]@row))), DATE(YEAR([C Date]@row) + 1, MONTH([C Date]@row), DAY([C Date]@row))), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!