Formula for date in cell OR date cell populated

Meredith Rhodes

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

Ryan Sides

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

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?

Meredith Rhodes

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)), "")