Pull month and year

Peggy Parchert
Peggy Parchert ✭✭✭✭✭✭
edited 12/14/20 in Formulas and Functions

Is there a way to pull the date and year from one column to another? I am currently using the following formula to pull the year out of the Report date column.

Report Column. Mandated Date Column

10/8/2020 =IFERROR(YEAR([Validation Report IA Date]@row) + 1, "")

I would like the "Mandated Date Column" to display the month/year (a year out). Is there a way to do this?

Any help would be appreciated.

Thanks, Peggy

Best Answer


  • MCorbin
    MCorbin Overachievers Alumni

    =(YEAR([Report Date]@row) + 1) + "/" + IF(LEN(MONTH([Report Date]@row)) = 1, "0" + MONTH([Report Date]@row), MONTH([Report Date]@row))

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    Thank you - one more question:

    I want it to read "MM/YYYY" - I tried moving the (YEAR([Report Date]@row) + 1) to the end but I'm getting a syntax error now. Is this not a possible format? I don't see an option like this in the dropdown for dates and I don't have the "Mandated Date Column" as a Date type - should it be?

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @MCorbin - is it not possible to have a format of MM/YYYY for this? I've switched the formula around but it keeps giving me an error message.

    Mandated Date Column is a text/number type (since I don't see MM/YYYY as a date option)

    =IFERROR(IF(LEN(MONTH([Latest IA Validation Date]@row)) = 1, "0" + MONTH([Latest IA Validation Date]@row), MONTH([Latest IA Validation Date]@row)) + “/“ + IF(YEAR([Latest IA Validation Date]@row) + 1)), "")

    I can get each part of this formula to work but I can't seem to make it work together.

  • MCorbin
    MCorbin Overachievers Alumni
    edited 12/15/20 Answer ✓

    @Peggy P

    Here you go:

    =IFERROR(IF(LEN(MONTH([Report Date]@row)) = 1, "0" + MONTH([Report Date]@row), MONTH([Report Date]@row)) + "/" + (YEAR([Report Date]@row) + 1), "")

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @MCorbin - thank you very much for the help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!