Cells with a formula date calculation are changing automatically...help!!

Amanda Merrigan

Hello all - I'm throwing this out there as it's completely stumped us and I'm hoping this has happened to someone else and they know what's going on!

We have a sheet that we have formulas created to calculate a backward scheduled start and end date for specific tasks (dependencies are not enabled). The February 8 date below is brought in from another sheet via index/match. (Note that I will try adding the files in a reply comment as I can't seem to do it on this original post). For some reason at 4:06pm yesterday, any time someone goes into the sheet/saves or ?? it triggers what looks like a recalculation of the formula and all the fields that have a formula in start/end target go blank, and then they go back to the original date calculated. What's funny is that looking at the sheet you never see the blank cell. Then at 4:34pm it stops.... I went into the sheet after that, made some changes, saved, exited, went back...nothing. And this morning it still hasn't happened. It wouldn't be a big except 1. It's really bugging me not knowing what's causing it! and 2. We have an automation set up to notify the assigned person if the date changes...and the blank does register as a change, so the person is getting notifications of dates changing, but no change shown. It's creating a lot of noise. I check the Activity Log to see if there is anything that could be triggering it and I don't see anything. (I'll try and post that as well).

Does anyone have any thoughts on this? I'm to the point where I might start from scratch & recreate the sheet, manually re-enter all the data and find all the reports it is referenced on and relink! Please help, because I REALLY don't want to do that! :)

*Edit - I cannot add a screenshot/file/image. It just keeps popping up the "add file" box to pick one even after I've done so. Any ideas there as well so I can add them to further help explain my situation?

**Turns out it's just that Edge is temperamental! Chrome worked fine...see below! :)

Amanda Merrigan

"Cells Changed (95)" at 4:06 above are just a bunch of changes related to the dates changing, nothing manually changed that would be a trigger for the mysterious "jumping".

Mark Cronk

Hi@AmandaM ,

I suspect the answer is in the formulas you're using to calculate the dates. Can you share a screenshot of those?

Today() is a common culprit. From the Today() function help:

Smartsheet will update all instances of the TODAY function in a sheet to the current date when any of the following actions are taken:

• You open the sheet and save it
• You add a new row to the sheet from a form submission
• You make changes to the sheet from an update or approval request (more on these alerts here)
• You update the sheet by way of a report (more on reports here)
• A linked cell updates the sheet (more on cell linking here)
• You open the sheet being referenced in a cross-sheet formula (more on cross sheet formulas here)

The following will not update formulas that are using the TODAY function to the current date:

• A report looking at the function in the sheet is opened.
• A dashboard showing information from the sheet is opened.

So, if you're using today() in sheets that aren't accessed regularly it doesn't update. When someone finally dies something to trigger a today() update, they all update.

Mark

Amanda Merrigan

Thank you for your response @Mark Cronk, that's very interesting information that I'll keep in mind, but in this case Today() is not the culprit. There are a couple variations of this formula used...=IFERROR(WORKDAY([Start Date (Target)]8, -1), "") and also this one...=INDEX({Operations Issued by}, MATCH(JOIN([Job Number]8:[Structure Dimensions]8, "_"), {WC-Structure ID}, 0)). I double checked our reference sheet, but the date referenced there is not influenced by a Today() either.

What's odd is that it's sporadic. We have multiple people that reference this sheet multiple times a day yet it doesn't happen daily.

Mark Cronk

Hi Amanda,

This is interesting. I don't have another guess. Your formulas look good. Nothing obvious that would cause a date issue. There are lots of smart people in the Community. Maybe someone else has an idea.

Mark

Ben Goldblatt

Hi @AmandaM,

What you're experiencing definitely sounds like strange and unexpected behavior and I highly recommend reaching out to the Support Team as they will be the best resource for troubleshooting this. If possible, any specific details you can provide when contacting them (e.g., sheet name, formulas used, screenshots or recordings of the behavior when the issue occurs, etc.) will be extremely helpful.

I hope this helps!

Thanks,

Ben