Freezing Dates
I am looking to have a blank cell populate a date in a column using an if statement to check for specific values in another column.
With excel I would normally use a circular reference to check if(and()) the cell was blank and that the values I want are in another column, and then populate a today(). With Smartsheet it calls out my bad practice circular reference.
Is there a way to have the cell update with the date and then freeze that date without using a circular reference?
Answers
-
Hi @Eternation,
A couple of ideas:
=IF(ISBLANK([Date Column]@row), TODAY(), [Date Column]@row)
This would set the destination cell to be either the date in the [Date Column] or today's date.
If you need to "freeze" the date to be today's date, but then lock that in, you could use a Sheet Summary value in the formula instead. You can use summary values in your formulas by referencing them as "[Value Name]#".
Would either of those options work?
Cheers,
Sam
—
primeconsulting.com
Thanks,
Sam
—
Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call! -
Would you be able to clarify summary value for me? The initial solution is my standard go-to with excel. It is a circular reference, however, and smartsheets is returning an circular reference error in return.
-
To clarify and close this out. I am an idiot. What ultimately solves this problem for me is a workflow to add the date whenever the specified cells meet the criteria I want to use to trigger the date. I, essentially, wound up using the workflow triggers as an IF function in the same way I would have written it, but without needing to write it as a function. I've brought too much of an excel mindset into this and had been overlooking some of the power involved with using the workflows.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!