Trigger Report Due when month changes from modified date

Steve Moss

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

Find more posts tagged with

Formulas

Reports

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…