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.
Smartsheet formula syntax help
I am trying to write a formula to add 9 months to date from another cell in my worksheet and am struggling with the formula syntax.
Adding days to a date is very straightforward, but I have not been able to figure out how to identify how to add months to a date.
Can anyone help with this formula?
Thanks, John
Comments
-
John,
As you probably know the Excel function for that problem is =EDATE(start_date,months)
It doesn't seem like Smartsheet supports the =EDATE formula.
I even tried to import an Excel workbook with that formula in it and I get the same error: #UNRECOGNIZED FUNCTION
-
This could be accomplished in a few steps with an IF statement. Starting with a formula like this: =DATE(YEAR(Date1), MONTH(Date1) + 9, DAY(Date1)) , 9 months are added to "Date1" date. The problem comes in when MONTH(Date1)+9 exceeds 12 months in a year. To account for this incorporate an IF statement that first checks in MONTH(Date1)+9 exceeds 12, if it doesn't use the formula above, else use the formula =DATE(YEAR(Date1) +1, MONTH(Date1) + 9 - 12, DAY(Date1)), which adds a year (YEAR(Date1) +1, and for the result of MONTH (Date1)+9-12) for the month.
Hope this helps!
Terry
-
Justin, you are correct in that the EDATE function does not work in Smartsheet.
Terry, you gave me a good thought process to try the nested IF function; but I am off somewhere... here is what I wrote =IF(MONTH([Purchase Date]1)+9 > 12), (DATE(YEAR([Purchase Date]1)+1, MONTH([Purchase Date]1), DAY([Purchase Date]1)), (DATE(YEAR([Purchase Date]1), MONTH([Purchase Date]1)+9, DAY([Purchase Date]1)))... IF the condition is true this would just add a 1 year and leave the month/day alone, IF the condition is false is just addes 9 months and leaves the year/day alone... it is not working as written though... have I made any glaring errors? Can I separate the two forumlas with the comma delimiter?
-
I forgot, I would have to subtract 3 from the MONTH value in the first argument, if I can get this formula to work.
-
Hi John, I thing just a set of parenthesis are out of place... also ENSURE that the formula is place in a column with the column property set to Date. And this formula seems to be working for me.
=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)))
Good Luck.
Terry
-
Terry, yes that worked!
Thanks so much for your help!
John
-
This is a great solution and helping me move in the right direction, really nice work and thank you!!
1) Would the inverse logic (-9 months ) be as easy as changing the first argument value from ">12" to "<12", the second argument from "+1" to "-1", the third argument from "+9 -12" to "-9 + 12", and the last argument from "+9" to "-9." This seems to make sense.
2)Thinking ahead how would you guys recommending moving from a hard-coded (value of 9 months) to a cell reference where a numeric month entered may be a positive or negative value?
Again, this has been extremely helpful, and thanks for helping me out!
Stan
-
So I tried the logic change for my first question, but was unsuccessful assuming because instead of using a hard coded value such as 9 months, I reference a cell to obtain that value (since this number is variable for my need but always negative at least) and my formula was Unparseable. I recall that the cell with my numeric month value is not a "Date" format column that could be causing the problem.
=if((Month([End date]2)-[period of notice(months)]2<12,Date(Year([End date]2)-1,Month([End date]2)-[period of notice(months)]2+12,Day([End date]2)),Date(Year([End date]2),Month([End date]2)-[period of notice(months)]2,Day([End date]2)))
Any suggestions would be appreciated.
Thanks,
Stan
-
Oops, meant "Invalid Value" as the error message and the formula had a missing parenthesis
=IF((MONTH([End date]2) - [period of notice(months)]2) < 12, DATE(YEAR([End date]2) - 1, MONTH([End date]2) - [period of notice(months)]2 + 12, DAY([End date]2)), DATE(YEAR([End date]2), MONTH([End date]2) - [period of notice(months)]2, DAY([End date]2)))
-
Sorry for multiple posts. Got it to work and tested it with hardcoding -9 months instead of + 9 months, but still need a solution for referencing a cell with a numeric value of the month as the variable to use.
Here is the functioning (hardcoded) formula for reference:
=IF((MONTH([End date]21) - 9) <= 0, DATE(YEAR([End date]21) - 1, MONTH([End date]21) - 9 + 12, DAY([End date]21)), DATE(YEAR([End date]21), MONTH([End date]21) - 9, DAY([End date]21)))
Thanks again,
Stan
-
Is Smartsheet considering implementing the EDATE function?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives