Formula Help

Options

I'm currently using smartsheet to store alot of data. I have one column label "PIO DATE". This field has dates corresponding to my data ex 1/2/2021. I need to add 4 years to this date in another column. Can someone assist me with a formula?


Thank you

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Super ,

    Try:

    =DATE(YEAR([pio date]@row)+4, MONTH([pio date]@row),DAY([pio date]@row))

    The formula deconstructs your PIO date, into a DATE function and adds 4 years to the year.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Joel Eldredge
    Options

    Mark, thanks for your comment. That does work if your entire column is configured to be a date. But if you have the column configured as Text/Number so you can enter in dates and numbers, all date functions return "#INVALID DATA TYPE".

    Basically, I want a date at the top of the column and then all my data is listed is cells below it.

    Any ideas?

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/09/21
    Options

    Hi Joel,

    Try:

    =YEAR([pio date]@row)+4) + "/"+MONTH([pio date]@row)+"/"+ DAY([pio date]@row)

    Change the order if you want a different date format.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!