Monthly Sequence

ullkay95

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.

jmyzk_cloudsmart_jp

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".)

https://app.smartsheet.com/b/publish?EQBCT=32f8257f5d1948bfa948da170231a0c5&_gl=1*5w8yka*_ga*ODkyOTI2MzM0LjE2OTI3MzY3MDc.*_ga_ZYH7XNXMZK*MTcwMzM3MjQ0NS4xMTcuMC4xNzAzMzcyNDQ2LjU5LjAuMA..&_ga=2.180602606.890939660.1703250255-892926334.1692736707

Demo Sheet to Caliculate EDATE Equivalent

https://app.smartsheet.com/b/publish?EQBCT=0b739c47c1374a28a92740026054d0b1