Date Formula that needs to run into following year

LFeliciano

I have a sheet with one column (start date), next column 3 months from Start, third column 6 months from start.

Formula for 3 months from start date is:

=DATE(YEAR([start date]@row), MONTH([start date]@row) + 3, DAY([start date]@row))

It's working fine for cells that are calculating for years 2025. For cells that will need to calculate 3 months forward from a date later this year, I'm getting an invalid value error in the cell. How do I adjust the formula above so that it counts into the following year?

Janae G.

Adding months is tricky- this comment chain explores this. I edited a formula I found there.

https://community.smartsheet.com/discussion/109494/add-months-to-date-formula

Something like this should work- you might need to play around with it to make sure it works for your needs.

Here the 5 is 6-1 in the FLOOR part of the formula. If you want 3 months, use 2, etc. Everywhere else the 6 is the number of months to add.

=IF(ISDATE([Start Date]@row), DATE(YEAR([Start Date]@row) + FLOOR((MONTH([Start Date]@row) + 5)/ 12, 1), IF(MOD(MONTH([Start Date]@row) + 6, 12) = 0, 12, MOD(MONTH([Start Date]@row) + 6, 12)), DAY([Start Date]@row)), "")

This accounts for rolling year, and Smartsheet automatically adjusts for invalid days (ex. 8/29/25 plus six months results in 3/1/25 not 2/29/25).

Hope this helps!