Total Age tracker overwrites last modified

Hi all,

Hoping someone out there can help and hoping its just something simple i need to change.

We use Smart sheets to monitor cases by age outstanding to help prioritise what needs looking at first.

so i have a formula to grab data from a cell says A1 that has the TODAY function in. and then take that away from the last modified date.

The issue i have is this creates a circle argument, as every day the date updates and so does the formula. when the formula updates it updates the last modified date so always comes back with 0 days since last update.

I was hoping this function would be able to detect it being a human update rather than a formula but doesn't seem to work this way?

Does anyone have a work around or fix?

Thanks,

Mike

Find more posts tagged with

Formulas

functionality

Automations

Accepted answers

All comments

Matthew J McAteer

Hi Mike,

Could you use a Create Date column which would remain fixed to the date the record was created?

Matthew

MikeT_Resideo

Hi Matthew,

I can and i use that for the total age of the case.

However we want it to then be able to say this case has not been looked at in X days and request the update from the Owner.

But due to the formula updating the Modified field aswell we cant

Matthew J McAteer

Hi Mike,

Assuming the definition of "has not been looked at in X days" means some or multiple columns have not been updated, you could add a Last Updated Date column, and create a workflow(s) that adds the date to the Last Updated Column when the column(s) are updated, This would give you a date that would stay fixed until the next update.

MikeT_Resideo

Hi Matthew,

Yes, any column within that row has not been changed.

i believe what you've suggested is what i currently have. the issue is though the workflow "=[TODAY FORMULA]$1 - Modified@row" this should be today IE 25/10/2023 - 24/10/2023(Day of last update) = 1 days.

However because this formula updates every day. So tomorrow for example should update to say 2 Days. but the Last updated date column detects this change and says the last update was today.

currently i don't see a way around this outside of telling staff to manually update a date column once they make a change.

Matthew J McAteer

Hi Mike,

If you use an Automated Workflow to add a date, the workflow can be set to trigger when a specific column or columns are updated.

If you add a Last Updated Date column, you can set up a workflow to add the date to that column based on the trigger of another column getting updated.

Since there is no formula associated with this date, the date added to the Last Updated Date column would remain the same until a new update is made to the trigger column. You can then use the date in the Last Updated Date to calculate the number of days that have passed since the update, and use that to trigger a notification to resource that needs to look at the case.

Hope that helps 😊

MikeT_Resideo

Ahh i never thought of doing it that way.

Thanks Matthew, been a great help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.