Formula to determine the date that another cell was populated

Sharon B

Hi,

I was trying to find an answer to a question that advised how I can show a date in one column that shows the date text was first added to a previously blank cell in another column.

So I have one column called "Date of response" and I want that to auto populate with a date when another field is first updated i.e. "Category team response" which is a text field.

I have seen that an enhancement request was submitted in 2018 for this functionality / formula, but not sure if was ever actioned.

https://community.smartsheet.com/discussion/37496/formula-to-determine-the-date-that-another-cell-was-updated

I also saw a post about using a system generated modified by date field but I don't seem to have this functionality available when I create a column - no idea why? But also this would auto update with every change so not a perfect solution.

https://community.smartsheet.com/discussion/68429/automatic-date-update-in-a-cell-when-another-cells-value-changes-as-calculated-by-a-formula

Any advice would be greatly appreciated.

Kind regards,

Sharon

Paul Newcome

@Sharon B You would click where it says "Add a condition to filter rows, select the date column you want populated, and set the condition as "is blank".

Paul Newcome

Have you looked into the Record A Date automation?

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

.

Sharon B

Hi that's great thanks. That certainly sorts it the first time it's updated, but if the cell is updated in the future, the date will update again and we just want to record the first time it something is reported. Is there any way to ensure this? i thought perhaps specify that the workflow only runs once, but that isn't an option and as there are many rows on the sheet that wouldn't work either.

Thanks,

Sharon

Andrée Starå

https://community.smartsheet.com/discussion/comment/443493#Comment_443493

Hi,

I hope you're well and safe!

You can add a condition that if the cell isn't empty, it shouldn't run and update the date.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Sharon B

Oooh let me have a look …thanks

Sharon B

@andree@workbold.com

Sorry if I am being a bit dim but had a look and am not sure how I can do this condition. The worflow I have is per attached screenshot, but if I try to add another condition I need to add another action and don;t knowhow to tell it not to do something - can only seem to tell it to do something.

If you could help advise how I build the workflow query it would be very much appreciated.

How can I add an action to say do not do anything.docx

Paul Newcome

@Sharon B You would click where it says "Add a condition to filter rows, select the date column you want populated, and set the condition as "is blank".

Sharon B

Hey @Paul Newcome I literally just sussed out that if I put a condition in to say only do it when the inital review field is blank then it will work. @andree stara also thank youso much both for your help. I was overthinking the last bit and it was so simple!!!

You guys are awesome. Thanks so much :)

Andrée Starå

https://community.smartsheet.com/discussion/comment/443507#Comment_443507

Happy to help!

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Paul Newcome

@Sharon B Happy to help. 👍️