IF AND OR Formula
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
-
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
-
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)))
-
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.
-
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)))
-
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!
-
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?
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 349 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!