# 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.

Tags:

• ✭✭✭✭✭✭
edited 12/23/23

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!