Trigger Report Due when month changes from modified date

Steve Moss

Hi

I have a basic text entry sheet for a dashboard report.

Fields are:

[Subject], [Details], [Modified], [Due]

 

In the [Due] Column I want to show that the month has changed so I can trigger an update request.

How would I go about doing this?

Regards

Steve

JeremiahHorstick

One way:

Create a formula column that is a like this: =if month([due]) <> month(today()), "Update Due", "Not Due". 

What I am doing here is saying if the due month is not the same as the current month set the formula cell as "Update Due" or "Not Due". 

***This assumes all tasks are always due on a monthly basis in the sheet & the due date for every task is updated monthly. Alternatively you could use logic such as "if the month for this task > the current month, it is due, however in January (Month 1) you will not be greater than December (Month 12). You will need to handle that exception. You could incorporate the year, or a complete date reference with logic to work smoothly through the end of the year.

Once your formula column is set up use the Alerts & Actions to trigger an Update request when the formula cell changes to "Update Due". 

The one thing about this method is the alert will only trigger when you open the sheet because that is the only time the formula updates. 

Let me know if I understood you correctly.

-J

Paul Newcome

You could set your Due column to a checkbox type and use 

 

=IF(MONTH(Modified@row) <> MONTH(TODAY()), 1)

 

That will check the box if the row hasn't been modified in the current month. You could then send your update request based off of the box being checked. As stated above though, the TODAY() function will only update when the sheet is opened.

Steve Moss

I don't have a due date in the column [Due]. I was intending to use the [Due] column to trigger with the formula.

I was hoping to get it to trigger without having to open it as I feel it's unnecessary admin.

I got #UNPARSEABLE because I have no date. The only date is in the [Modified] column, which is the system Modified Date.

Is there another way of doing this?

 

Regards

 

Steve

Paul Newcome

See my post below in regards to using the Due column as the trigger. unfortunately though, when using TODAY() in a formula, it only updates when the sheet is open. If you only want the notifications sent out weekly or monthly, you could set a reminder for yourself to open the sheet the day you want the reminders to go out.

JeremiahHorstick

Paul, If the sheet is updated via form, a linked cell,  API or anything else will that cause the sheet's formulas to calculate and then potentially send the request?

I vaguely remember that being the work around. One sheet with a link to all others...

Steve Moss

I go this to work.

 

I modified it slightly to:

 

=IF(MONTH(Modified@row)<>MONTH(TODAY()), "Current", "Report Due")

Works fine.

 

But would be good to sort out the TODAY() calculation needing to be opened via the sheet. Bit of an admin issue.

Paul Newcome

Glad you got something working for now. I do agree though. The TODAY() thing could use some improvement.

Paul Newcome

I am honestly not too sure. I don't really work with API's, but I will do some testing with linking and whatnot to see if I can get something to work that way. It may not be for a little while though.

Andrée Starå

Hi Jeremiah,

Is the workaround you were thinking of?

Yes, you'd link from the "Master Sheet" to all others that need to be "updated" daily. Of course, it doesn't need to be automatic with Today(), so you could also manually trigger the "update".

Best,

Andrée

JeremiahHorstick

Here's the example of how to use Zapier to update the sheets daily. I've never tried it. 

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

Craig brought it up in a conversation before.