Why does my Modified (date) column update every row, with every update?

Ryan W

I have a sheet made for tracking the inspections of our work vehicles so that no one double checks a vehicle. Anytime someone updates the row for a vehicle, the entire column updates to that time, so it looks like all the vehicles have been checked that day.

I never had any formulas using =TODAY().

I've had to have them manually update the time and date as a temporary fix until I can find a solution.

I've been pulling my hair for the past few days over this and have been trying to find a solution.

Paul Newcome

Can you list out each of your formulas within the sheet?

Ryan W

I had a formula to get an accurate date because at 8pm every day it recognizes all new data as tomorrow.

=DATEONLY([Last Time Checked]@row) - IF(AND(VALUE(MID([Last Time Checked]@row, 10, FIND(":", [Last Time Checked]@row) - 10)) >= 7, FIND("P", [Last Time Checked]@row) > 0), 1)

So I had found that and it has worked great across multiple sheets, but this sheet seemed like it was updating the sheet anytime someone other than me opened it. But I could never reproduce it. I had a team member update the sheet on their computer and I watched and it worked fine. I figured all was good, later that evening, it would update every "Modified (date)" row and also "Modified By" row even if nothing was addressed in those rows.

I also had =RIGHT([Last Time Checked]@row,8) to get an accurate time checked for a different sheet because when it would copy the rows over to another sheet for archival purposes, the [Last Time Checked] would default to That time and not when it was officially checked.

I hope I made sense, haha.

Paul Newcome

What is being populated in the Modified By column when it does this automatic update?

Ryan W

It has the email address of the last person to open the sheet.

This is what has been happening.

-Joshua viewed sheet @ 8:39PM, he changed a few fields accurately, no where near 90. The conditional formatting is accurate as well. It then updates the modified by and modified date column at every row, even if he didn't touch those rows.

-11PM, it auto copies every row to a separate sheet for archival purposes.

-1:23am, yes that early, Gary gets on to check out what has been checked, he doesn't update or click on any cells, yet they all switch to that time and modified by him.

-I log on this morning to see that it's all continued to be fubar.

Ryan W

I am currently just building an entirely new sheet, hoping that maybe there is literally a glitch with that particular sheet and this new one will work.

Paul Newcome

Ok. Keep us posted on the new sheet.

Ryan W

I've had a few people helping me test it, and I feel that one of the 2 formulas is actively updating every time the sheet is opened.

Either

=RIGHT([Last Time Checked]@row, 8)

=DATEONLY([Last Time Checked]@row) - IF(AND(VALUE(MID([Last Time Checked]@row, 10, FIND(":", [Last Time Checked]@row) - 10)) >= 7, FIND("P", [Last Time Checked]@row) > 0), 1)

I've since made up different combinations of some rows having both formulas, some with one or the other, some with none and the ones with no formulas haven't auto updated. So it seems that if any formula is referring to the modified date column, then it will update every time.

So now I am going to have to figure out a way of recording the time when it gets copied to the archival sheet every night.

Paul Newcome

If you need to record the time that it gets copied over to another sheet, you can add a Created (date) column on the destination sheet.

Ryan W

https://community.smartsheet.com/discussion/comment/276729#Comment_276729

I don't need the time it gets copied, that is at 11pm every evening, I would like to be able to copy over the time it was last updated on the original sheet. But I just will look at the cell history on the original sheet if we need it.

Paul Newcome

Ah. Ok. What if you set up a lock row automation to trigger as soon as the row qualifies for the copy row? That way it locks it right away and then should be static once the copy row kicks in.