Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula Syntax Help
Hello,
I am creating a sheet to track calibrations for the equipment in our department. I saw the function to add months to a date from another cell
https://community.smartsheet.com/discussion/smartsheet-formula-syntax-help
=IF((MONTH([Purchase Date]1) + 9) > 12, DATE(YEAR([Purchase Date]1) + 1, MONTH([Purchase Date]1) + 9 - 12, DAY([Purchase Date]1)), DATE(YEAR([Purchase Date]1), MONTH([Purchase Date]1) + 9, DAY([Purchase Date]1)))
I want to subtract 2 months rather than add them to the date in the Calibration Due column. When I entered the function into the Calibration Reminder cell it worked to subtract 2 months, but when the value has to change over the year ( EX: 2017 to 2016) it returned with #INVALID VALUE and I am not sure what is wrong. I was hoping someone would check my function and see where my error is. The function I used is below and I have attached a screen shot for reference.
=IF((MONTH([Calibration Due]24) + -2) > 12, DATE(YEAR([Calibration Due]24) + 1, MONTH([Calibration Due]24) + -2 - 12, DAY([Calibration Due]24)), DATE(YEAR([Calibration Due]24), MONTH([Calibration Due]24) + -2, DAY([Calibration Due]24)))
Thank you,
Morgan
Comments
-
This is your formula
=IF((MONTH([Calibration Due]1)) < 3, DATE(YEAR([Calibration Due]1) - 1, (MONTH([Calibration Due]1)) - 2 + 12, DAY([Calibration Due]1)), DATE(YEAR([Calibration Due]1), MONTH([Calibration Due]1) + -2, DAY([Calibration Due]1)))
You were off by one +/- sign.
https://app.smartsheet.com/b/publish?EQBCT=6c2376db9342493388b82c6ab6d691cb
-
Brett,
Thank you for your help on this it works great!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives