Automate update requests for every week if status is not 100%

dave.mcpherson56751

Hi All,

Is it possible to set up automated update requests until such time that the particular task is complete?

What i am trying to achieve is:

I have a large project involving 360 sites, i have created a sheet with 360 rows and 8 columns of status for different tasks. When a column is complete it adds 12.5% to the % complete column per row.

I would like to send an update request out every Friday to the "Allocated to" recipient if the % complete is not 100%

Any help or guidance would be gratefully received.

Thank  you

Dave

Andrée Starå

Hi Dave,

One way to do it would be to have a column with the date for when you want the update request to be sent. The first row of that column or in another cell you put the date of next Friday and then link that to all the rest of the cells in the update request column.

Set up an update request for yourself or someone else to update that date every week. Set up another rule for the update request to be sent to everyone else.

Not automatic but a good workaround.

It's also possible with the help of a third party solution like Zapier to automate it.

Would that work?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

There's got to be a way to automate the date with a formula. I've been trying to figure it out for a while now. I generally end up building a table and using an INDEX/MATCH to pull the appropriate date, but I have to open the sheet every morning for it to update since I use the TODAY function. There will also eventually come a time where I have to adjust the table as it is limited to how far out into the future I can build it.

Andrée Starå

There is always a way!  It just depends on how much work it takes compared to the value it provides.

I'm hopeful that we soon* will have time capability natively and then it will probably be a lot easier to build this kind of workflows.

*A relative term for time, one month to six months is my guess.

Paul Newcome

=TODAY(IF(WEEKDAY(TODAY()) = 1, 5, IF(WEEKDAY(TODAY()) = 2, 4, IF(WEEKDAY(TODAY()) = 3, 3, IF(WEEKDAY(TODAY()) = 4, 2, IF(WEEKDAY(TODAY()) = 5, 1, IF(WEEKDAY(TODAY()) = 6, 0, 6)))))))

 

This will always give you the date for the next upcoming Friday regardless of month/year changes. If today IS Friday, it will give you today's date. If today is Saturday it will give you the date for next Friday. 

 

Apparently I was over thinking it by A LOT... -_-

 

You could then set up your update request to send on a date field using the the date provided from above and add in your criteria of if % Complete is not 100%

Paul Newcome

Another idea... Build the below table (Column names are in bold):

 

IfTodayIs          NextFridayIs

1                        5

2                        4

3                        3

4                        2

5                        1

6                        0

7                        6

 

You can then use the formula of 

 

=TODAY(INDEX(NextFridayIs:NextFridayIs, MATCH(WEEKDAY(TODAY()), IfTodayIs:IfTodayIs, 0)))

 

....................

 

Why is it that the simple stuff is so hard to figure out sometimes? 

Andrée Starå

 Nicely done!

Important to note that the sheet needs to be opened for the formula to update for the TODAY function.

Paul Newcome

@Andree

 

Thanks!

 

Important to note that the sheet needs to be opened for the formula to update for the TODAY function.

 

Yes. Definitely a good thing to keep in mind. I'm hoping to figure out a way to do it without that little hitch, but that has moved pretty low on my list of priorities now that the more primary issue has been resolved.

 

I'll just call the column the formula is in "Friday" and in row 1. You could then plug in elsewhere on the sheet

 

=DATE(Friday1) + 3

 

That would give you the following Monday's date. Have a reminder set up to send on the Monday date to open the sheet. When you open it Monday, it will update Friday's date to that week's Friday and Monday's date to the following week's Monday. 

 

You get a reminder to open it up once a week which will reset the dates to the correct ones. BAM!