# My formula doesn't work when I choose the first of the month as Effective Date

Options
✭✭✭✭

this is my formula:

=IFERROR(DATE(YEAR([Effective Date]@row) + ROUNDDOWN((MONTH([Effective Date]@row) + ([Contract Term (Months)]@row)) / 12, 0) + IF(IF(MOD(MONTH([Effective Date]@row) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)) = 12, -1), IF(MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)), DAY([Effective Date]@row) - 1), "")

Tags:

• ✭✭✭✭✭✭
Options

Hi @WinaHath,

What result are you expecting if you put the 1st of the month in the effective date?

• ✭✭✭✭
edited 01/16/24
Options

it is supposed to take the number in the contract term column, add that to the effective date, minus 1 day to output the End Date

• ✭✭✭✭✭✭
Options

Using your above data as a sample, is this correct (albeit in different date format)?

New formula:

=IFERROR(DATE(YEAR([Effective Date]@row) + ROUNDDOWN((MONTH([Effective Date]@row) + ([Contract Term (Months)]@row)) / 12, 0) + IF(IF(MOD(MONTH([Effective Date]@row) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)) = 12, -1), IF(MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)), MAX(DAY([Effective Date]@row - 1), 1)), "")

The 1st of the month is failing in your formula as you can't have the 0th of the month (from DAY([Effective Date]@row) - 1) ), so the MAX portion of the formula fixes by making any 0s into 1s.

Let me know if I'm barking up the wrong tree with this or if you've any problems/questions.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!