Formula for date in cell OR date cell populated

Options

Good afternoon!

I have a column on my sheet called 'Submission Date' that records the date a task was submitted for review. Most often there will be an actual date populated in that column.

But once in a while, the team will record 'N/A' if the particular task does not require review.

I have a column with a formula that collects the Year for the submission date:

=IFERROR(YEAR([Submission Date]@row), "")

But, if N/A is populated in the submission date column - I wouldn't mind collecting the Year that someone populated that cell - so I can keep track of how many total tasks were completed in a given year.

What is the easiest way collect the Year the cell is populated? Can I throw that logic into the IFERROR wrap of my formula so that it collects the submission year or the year the cell was populated if the submission year is in error?

Thank you!

Meredith

Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Meredith, you’ll need a second column where you can capture the year. An automation to “record a date” in your new column when the Submissions Date field changes would do it.


    then you can point your formula to check if the Submission Date is equal to N/A then get the year from the new year column. If it isn’t N/A, get the year from the Submission Date.


    Does that help?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Meredith, you’ll need a second column where you can capture the year. An automation to “record a date” in your new column when the Submissions Date field changes would do it.


    then you can point your formula to check if the Submission Date is equal to N/A then get the year from the new year column. If it isn’t N/A, get the year from the Submission Date.


    Does that help?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Meredith Rhodes
    Options

    Success! Thank you Ryan :)

    For future reference, this was my formula after creating a Submission Date Stamp Automation:

    =IFERROR(IF([Submission Date]@row = "N/A", YEAR([Submission Date Stamp]@row), YEAR([Submission Date]@row)), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!