I want to know how many rows have changed in the last 30days from a table.

Afolakemi Jedidiah · 04/12/22

So I have a sheet that is been updated. I would like to write a formula that shows if a particular column changed in the last 30days/ 60 days? Does anyone know to write this formula? Thank you

Paul Newcome · 04/12/22

You can group triggers together if you wanted an "or" type of scenario such as Column A changes to any value or when Column B changes to any value.

If you needed to track the columns separately then you would need additional helper date columns and set up automations for each.

You are also correct that this will only work going into the future. It will not grab anything that has already been completed.

If you need to grab what has already been completed then Highlight Changes will give you a visual indicator, but getting actual dates will require manual entry along with some digging through/filtering of the activity log.

AnthroTim · 04/12/22

Actually - I misread your question - you want a formula that tells you if a COLUMN has changed in the last 30/60 days!

The answer below is for a ROW! I'll need to have a think about how to do it for a column!

You can also look at Highlighting changes:

https://help.smartsheet.com/articles/521974-highlight-changes-made-to-a-sheet

Apologies.

---------

Hi,

There's a few approaches here, but this one is pretty simple and might give you what you need. You can also look at Highlighting changes:

https://help.smartsheet.com/articles/521974-highlight-changes-made-to-a-sheet

First, in the table make sure you have an 'Auto-Number/System' column set to 'Modified (Date)'.

Then create another column called 'Days since last change' and add the following as a column formula:

=TODAY() - Modified@row

This gives you the number of days since the last update.

Finally, add a couple of conditional formatting rules that highlight the row if it's 30 days or more and one if it's 60 days or more.

You can then easily see which rows have or haven't been updated in the last 30/60 days.

Tim

Afolakemi Jedidiah · 04/12/22

@AnthroTim Thank you for your response. The Auto-Number/System I have just created- will this be serial numbers and. The formula for the "days since last change" doesnt seem to work coming back as unparseable. I typed it =TODAY() - Modified@row and also changed it to =TODAY()- Modified(row1:row10) ,didnt work either .

Andrée Starå · 04/12/22

Hi @Afolakemi Jedidiah

I hope you're well and safe!

This might help.

Please have a look at my post below with a method I developed.

More info:

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome · 04/12/22

Why not just set up a basic Record A Date automation where the trigger is set to that specific column? Recording the date in a separate column will have the dates listed on each row that has changed, but you can pull the MAX date to show you when the column was last updated.

Afolakemi Jedidiah · 04/12/22

@Andrée Starå Thank you but your response hasnt helped me. So what I need is a formular that tells me if a row or column has changed in the past 30 days. If true Inserts True in the column or else false.

Andrée Starå · 04/12/22

@Afolakemi Jedidiah

Happy to help!

Paul's suggestions above would probably be the best option if you only need the day and not the time.

Afolakemi Jedidiah · 04/12/22

@Paul Newcome Thank you. So trying to check for specific columns. Using the trigger implies I have to set up a trigger for 20 columns. Even at that, If I have to set this up it will only work from today and not previous changes. So hopefully there is a better solution.

AnthroTim · 04/12/22

Hi @Afolakemi Jedidiah ,

fir the formula I supplied you'll need to make sure the Modified@row matches the name of your new Auto column, where it is storing the modified date.

I'm on my phone at the moment but will try and add a screen grab when back at my desk to show you what I mean

Bear in mind my solution only highlights the row, it won't tell you if a specific column/cell has changed. Just that there was an update to a value in that row

Afolakemi Jedidiah · 04/12/22

@AnthroTim Yes that makes sense which I already have . i am need the specific column/ cell. if possible. Thanks so much

AnthroTim · 04/12/22

@Afolakemi Jedidiah - In that case then there's no particularly nice way of handling this as far as I can work out.

@Paul Newcome's suggestion of using automation to track the date that a column changes would work, but if you want to track all the columns then you'd have to have a whole set of shadow columns that recorded the date that they were updated.

So if you had a Column that you wanted to track called 'Column A' you'd have another column called 'Column A Last Updated' (or something like that). Then one for each column you wanted to track. You'd then need an automation for each one of those columns that tracked the column (Column A) and set the date in 'Column A Last Updated'.

If you had that you could then use 'Conditional Formatting' to highlight on the cell in Column A that had an updated date in 'Column A last Updated' that was in the last 30 days etc. (Or you could again use the days since last update formula I mentioned previously to make it a bit easier and more flexible).

BUT you'd end up with lots of automation rules, a load of extra columns (that could be hidden) and a whole load of conditional formatting rules. So it's not a NICE solution by any means! And it would need to be updated if you added new columns etc. So is not great from a maintainability perspective.

So, I guess the question is. what do you want to achieve and why (business benefit)? As it's quite a cumbersome solution.

If all you need to do is see what fields have changed in the last 30 days then turning on 'Highlight Changes' may be enough:https://help.smartsheet.com/articles/521974-highlight-changes-made-to-a-sheet

Also, do you need to track every column and/or every cell? If it's a limited subset, then building something with the automations described above may be the route to go.

Good luck

Tim

Paul Newcome · 04/12/22

You can group triggers together if you wanted an "or" type of scenario such as Column A changes to any value or when Column B changes to any value.

If you needed to track the columns separately then you would need additional helper date columns and set up automations for each.

You are also correct that this will only work going into the future. It will not grab anything that has already been completed.

If you need to grab what has already been completed then Highlight Changes will give you a visual indicator, but getting actual dates will require manual entry along with some digging through/filtering of the activity log.

Afolakemi Jedidiah · 04/12/22

@AnthroTim Your solution kind of helped but what I have noticed is that it is taking into cognisance my activity today with the Modified date and every thing in the days last change is coming back as 0. I had also set up the automation for the rows before, but triggered by an action which I have noticed has not been triggered( checkbox action). The goal is to show analysis of no of rows that has been updated against those that have not and get an action for updates.

I want to know how many rows have changed in the last 30days from a table.

Best Answer

Answers

Help Article Resources

Categories