Setting Automated Alerts based on last modified date column

BillDav

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?

Paul Newcome

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.

Paul Newcome

Are you putting the formula in a date type column of its own?

Paul Newcome

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.

Jeana

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

BillDav

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?

Paul Newcome

Are you putting the formula in a date type column of its own?

BillDav

Paul,

Ding! I was too far in the weeds and not looking at what type of column the formula was populating. Thank you sir!

Paul Newcome

Haha. I do that all the time. No worries. Happy to help! 👍️

tylermh

@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!

Paul Newcome

@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.

jcarter

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.