Date calculation formula not working

Two date columns

Last Service and Next Service

I want "Next Service" date to be "Last Service" date plus one year.

I created the following formula in the "Next Service" column:

=IF([Last Service]@row <> "", DATE(YEAR([Last Service]1) + 1, MONTH([Last Service]1), DAY([Last Service]1)), "")

It correctly calculates the Next Service date in row 1, but when I try to convert to column formula I receive this message:


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Tanya Brooks

    It's because you're referencing a specific row number into the formula, and it's not possible with column formulas.

    As a possible workaround, you could add that reference to the sheet summary section instead and use that in the column formula.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!