Update triggered by day of month, repeat daily until complete

DanR

Sending a single update request does not always generate a response because many people are not good about answering all their emails, and then it gets lost in old email. I'd like to generate a daily update request that begins on a set day each month (like the 14th) and repeats daily until the person completes the update request. So far my only solution is a manual reset of a trigger to generate a daily update request, but that's too complicated for this smartsheet because the update request needs to start on different days for different lines. Some need to start on the 20th, others on the 25th. Is there a formula I can use to change a status field based on the day of the month, then change it again after the update response? I thought I could do it with the TODAY and DAY functions, but TODAY only updates when the sheet is revised or saved. A dormant sheet would miss the trigger.

Andrée Starå

Hi Dan,

There are a few ways to set it up!

Is it always the same cell(s) that are updated? Are they always empty before the update?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

 

DanR

Hi Andree. This is a monthly invoice tracking smartsheet. I have 5 categories of invoices that are processed slightly differently. The best example is the standard AIA invoice that bills on the 20th, or the 25th, or the 30th of the month. Five days before the billing deadline of, say the 20th, I'd like to start the daily update request which repeats until the PM responds. The PM is supposed to update their account in the company accounting software, then return to the update form and update the "Monthly Invoice" field using the dropdown list: "Ready to Invoice" or "Do Not Invoice." 

The "Monthly Invoice" field will be manually reset each month. I realize this can be used as a trigger, but it would have to be done on the day the daily messages need to start. (I'm currently using it as a condition of the trigger.) Since I have numerous categories intermixed on the sheet, this would be complicated. While I could use filters to make it simpler, it still means someone has to be responsible to actually do it on that particular day. But with vacations and sick days, etc, I can't count on that. I want to completely remove the human trigger. They're not reliable.

The automation allows me to do a monthly update request on one particular day of the month. But that's only one email. I can set it to daily, but then I need something in the sheet to trigger the conditions. I can't find a function that will trigger a change on a selected day of every month. 

I shared the sheet to you. I hope this more detailed description helps. Thanks.