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?

Tags:

Answers

  • Sam_Harwart
    Sam_Harwart ✭✭✭✭✭
    edited 05/07/24

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!