Add month to Date
Hello,
Please help me why when the "C Date" is December, and the "IN Freq" value is "12 Month" I got an error "#INVALID VALUE" and it works if "C Date" is December and the "IN Freq" value is " 6 Month" with the formula below:
=IF([C Date]@row = "", "", IF([IN Freq]@row = "", "", IF((MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1))) > 12, DATE(YEAR([C Date]@row) + 1, MOD(MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1)), 12), DAY([C Date]@row)), DATE(YEAR([C Date]@row), MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1)), DAY([C Date]@row)))))
Thank you!
Answers
-
@BonW Whatcha trying to do?
dm
-
Hello Dale,
Thank you for response to my post. I'm trying to automate "Inspection Date". If "IN Freq" value is "6 Month" I like to add 6 months to the "C Date" and populate the date value to the "Inspection Date" and if value "12 Month" I like to add 12 months to the "C Date" and populate the date value to the "Inspection Date". For some reason when the "In Freq" value is "12 Month" and the "C Date" month is December the system displayed the "#INVALID VALUE" other months are working and not December month.
=IF([C Date]@row = "", "", IF([IN Freq]@row = "", "", IF((MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1))) > 12, DATE(YEAR([C Date]@row) + 1, MOD(MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1)), 12), DAY([C Date]@row)), DATE(YEAR([C Date]@row), MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1)), DAY([C Date]@row)))))
Thank you!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.7K Get Help
- 371 Global Discussions
- 205 Industry Talk
- 436 Announcements
- 4.5K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 129 Community Job Board
- 448 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives