Automatic date update in a cell when another cell's value changes (as calculated by a formula)
Hello
I would like to have a field set up that updates the date every time the 'status' changes in another column. Is this possible?
For example, when a new line item is added and the status column changes to 'on hold' I would like the date the status changed to show in another column. This date will stay the date since the status was put to 'on hold' and the date will only update when the status changes again.
Best Answers
-
When you go into the column properties to change the column type, or when you create a new column. you can select the system generated type and there will be an option for a Modified (date) which will output the date/time that something in the row was last changed.
-
Not necessarily. As long as there are two rows of the same hierarchy immediately above or below that contain the formula, it will autofill when the new row is added.
Answers
-
Have you looked into the system generated Modified (date) column type?
-
No, I also do not know what that is. Do you mind explaining?
-
When you go into the column properties to change the column type, or when you create a new column. you can select the system generated type and there will be an option for a Modified (date) which will output the date/time that something in the row was last changed.
-
Awesome! That should work perfectly.
I have a follow up question now. I would like to set a reminder for one month or bi-weekly from the modified date column. However when I choose the trigger "when a date is reached" I select the date field and the modified date field does not show up. How would I create a reminder based on the modified date?
-
Create a helper column that can then be hidden to keep the sheet looking clean. In every row of this helper column use
=DATEONLY(Modified@row)
-
I'm assuming this equation will have to be dragged down whenever a new row is added?
-
Not necessarily. As long as there are two rows of the same hierarchy immediately above or below that contain the formula, it will autofill when the new row is added.
-
We are looking to do this also, so that we can create a filter of all RAID items that have a status change from Open to Closed in the past week. We are already using the modified by column to show all changes, but are looking to have a column that will only show the date when the status column changes. Is this possible?
-
@Melissa Kosiewicz It is not possible directly within Smartsheet, but it is possible using 3rd party apps such as Zapier. I do not know a whole lot about that particular app, but I have seen it recommended quite a few times for this particular situation. @Andrée Starå knows much more about it than I do.
-
Thanks @Paul Newcome
There are two options. As Paul mentioned, Zapier or, if possible, we can keep it in Smartsheet with a solution I developed where you can store the date/value.
You'd use the copy-row automation and a VLOOKUP or combination of INDEX/MATCH to make it work.
We'd trigger the copy-row to another sheet and get the created date/other value and then use the VLOOKUP/INDEX/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id/values that we can use, it will work.
Also, this opens up more options with auto-numbering and more.
Make sense?
Would any of those options work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå I had thought about the copy row automation, but I must have gotten my posts mixed up. I had remembered reading somewhere (apparently somewhere else) about wanting to avoid using the extra sheet.
-
Been there done that! Easy to get it mixed up.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå - Is this still the best solution - I was looking for a solution for something very similar as above and would like to avoid creating a duplicate sheet or using a third party app.
Thanks!
Thanks,
Sravya
-
I would say yes.
Here’s a post on the method I developed.
Would that work?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you @Andrée Starå - I do have auto numbered row ID that I have used but couldn't quite get the index match working as there are still multiple rows with the match. So, I used max and collect function instead to get the last updated value.👍️
Thanks,
Sravya
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!