Monthly Sequence
Hi - uploaded an excel sheet to Smartsheet that is not compatible with a formula. The formula in excel was: =EDATE([Column9]14,1) which calculated the next month (1/1/2024, 2/1/2024, etc.) what is the smartsheet version of this? I can't drag the dates because it is a row and not column of dates.
Answers
-
Hi @ullkay95
You can get an equivalent of EDATE by this formula;
=DATE(INT(YEAR(Date@row) + (MONTH(Date@row) + M@row - 1) / 12), MOD(MONTH(Date@row) + M@row - 1, 12) + 1, DAY(Date@row))
To use your Excel "=EDATE([Column9]14,1)" formula's arguments, [Column9] and 1, modify as follows;
=DATE(INT(YEAR([Column9]@row) + (MONTH([Column9]@row) + 1 - 1) / 12), MOD(MONTH([Column9]@row) + 1 - 1, 12) + 1, DAY([Column9]@row))
This formula is valid for any future month and previous month up to 11 months ago. (See the image below, row 11)
If you want a valid formula for any previous months, here is the formula;
=DATE(INT(YEAR(Date@row) + (MONTH(Date@row) + M@row - 1) / 12), IF(MONTH(Date@row) + M@row = 0, MONTH(Date@row), ABS(MOD(MONTH(Date@row) + M@row - 1, 12) + 1)), DAY(Date@row))
If you are calculating only future months, I recommend the first formula, which is shorter.
The 3rd formula for EDATE 9 is taken from Smartsheet's official Function Handbook, in which you will find many interesting examples. (In the template set, go to the Advanced Formula Examples sheet and look for "Return a date n months in the future".)
Demo Sheet to Caliculate EDATE Equivalent
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!