Add 3mth date increment to 'date completed'

Hello, i'm hoping someone can assist me with a formula that i'm entering which returns an invalid error. I have scoured the help forum for assistance, but not had any luck replicating the formulas in my sheet.

What i want to do is automatically generate a date which is 3mths after the date entered in the 'date completed' column.


Best Answer


  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭

    Hi @atotsidis,

    My initial thought is for you to use a helper column to determine the month 3 months later and use that to determine the new date. Here is a screenshot of the results - in real use you would probably hide the helper column.

    Helper column formula: =IF(MONTH([Date Completed]@row) + 3 > 12, MONTH([Date Completed]@row) + 3 - 12, MONTH([Date Completed]@row) + 3)

    Post Completion column formula: =DATE(YEAR([Date Completed]@row), [3mths later (helper)]@row, DAY([Date Completed]@row))

    There may a more elegant solution, but this is what I came up with for now and it seems to be working.

    Hope this helps,


  • Thanks so much Dave. I haven't explored this helper column route before. Help me understand, exactly what values do i need to enter into the helper column? I see that the center column highlighted in blue does provide the accurate dates 3 mths post completion, but i cannot make sense of the values in the helper column. can you elaborate here?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You shouldn't need a helper column. Try this:

    =IFERROR(DATE(YEAR([Date Completed]@row), MONTH([Date Completed]@row) + 3, DAY([Date Completed]@row)), DATE(YEAR([Date Completed]@row) + 1, MONTH([Date Completed]@row) - 9, DAY([Date Completed]@row)))

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    You can skip the helper column:

    =IF(MONTH([Date Completed]@row) + 3 <= 12, (DATE(YEAR([Date Completed]@row), MONTH([Date Completed]@row) + 3, DAY([Date Completed]@row))), (DATE(YEAR([Date Completed]@row) + 1, MONTH([Date Completed]@row) - 9, DAY([Date Completed]@row))))

    As you can see from the example, if the month you're using is shorter than the month 3 months from the date it will move the date appropriately.

    Or if you're not pick about it being exactly 3 months but 90 days, then this will work:

    =[Date Completed]@row + 90

    Pick whichever option appeals! 😊

  • @Paul Newcome unfortunately the formula didn't work. I copied and pasted it but it returns an invalid message.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @atotsidis That error comes from trying to output a date into a non-date type column. Try changing the column type to a date type.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!