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
Check out the Formula Handbook template!