IF AND OR Formula

BonW

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

Paul Newcome

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

Paul Newcome

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

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

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

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

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

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

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