Setting Automated Alerts based on last modified date column
Hello team,
I am looking to set up an automated alert based on the last modified date column within my sheet. The purpose being an alert sent out to my team on entries that have not been updated within the last 3 days. I have not found a way to do this and after searching the forums I also have not found an answer for this. To me this seems pretty straight forward, but because I can't select last date modified as an option for updates based on date I am stuck. Any suggestions?
Best Answers
-
You would need to create a helper Date type column and use
=DATEONLY(Modified@row)
This will pull the date into an actual date column that can be used for your Alert.
-
Are you putting the formula in a date type column of its own?
Answers
-
You would need to create a helper Date type column and use
=DATEONLY(Modified@row)
This will pull the date into an actual date column that can be used for your Alert.
-
I'm looking for something similar but I need to specify in the automated alert the row for that column. I can specify the Helper date column but what row will it reference?
Jeana
-
Paul,
Thanks for the response. I believe the problem, and I could be wrong, is that I am using the automated last date modified field to populate the helper date formula. When I do this I receive:
#INVALID COLUMN VALUE
Looking at the help page for this formula, it states "Currently only values in Date/Time columns in dependency-enabled project sheets and System columns include a time component," which to me means that although the last modified field shows a time component, that it is not considered under the above dependency.
Some additional information regarding System Columns:
System column values are read-only for all collaborators, but you can apply formatting (for example, background and font colors) to them manually or by creating Conditional Formatting rules.
So I am back at square one. Is there another way to create a last modified date column that is usable for alert creation?
-
Are you putting the formula in a date type column of its own?
-
Paul,
Ding! I was too far in the weeds and not looking at what type of column the formula was populating. Thank you sir!
-
Haha. I do that all the time. No worries. Happy to help! 👍️
-
@Paul Newcome Would you mind posting a screen shot of the workflow for this so I can see how this would work for what I'm trying to accomplish? I basically want people to be notified if 6 days have gone by without a row being modified. Thanks!
-
@tylermh You would need to start by adding a date type column to your sheet. In this column you would enter something along the lines of...
=DATEONLY(Modified@row) + 6
Then you would set your automation to be triggered based on a date field and select this new date column.
-
Just be careful here and don't do what I just did. When you create that helper column, the Last-Modified column will all change to the date you create the helper column and your Last Modified date is gone. The only safe way to do this is to establish that helper column when you are starting the sheet. Not when it is already built.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!