Formula for date in cell OR date cell populated
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
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
Best Answer
-
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?
Answers
-
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?
-
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)), "")
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!