Has anyone found workaround today() doesn't involve zapier

azaidi102336

We want functionality where "Milestones missed in Last 30 days" are highlighted. We can identify Milestones. What functionality do we use to count the rows that have missed the milestone?

I've seen responses about Today() function having a daily-touch limitation. I'm looking for the exact Logic to get Milestones missed in the last 30 days. I have seen this (similar) example, for projects that went live in the past 30 days:

=COUNTIFS({Projects - Go LIve}, <TODAY(1), {Projects - Go LIve}, >TODAY(-30))

Andrée Starå

Hi,

Do you have a sheet that you open and update daily?

Another workaround could be to use a daily update request and that would trigger the update. I have clients that use that when they don't want or can't use Zapier.

Would that work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Genevieve P.

Hi,

If I understand you correctly, you're looking for a way to call-out if a task has an end-date from the past 30 days and yet it is not completed (therefore the milestone has been missed). 

An alternative to using TODAY in a formula on your sheet would be to create a Report from this sheet, instead. You could set the criteria of the report to only pull rows where the Status is still In Progress and the End Date is within the last 30 days. I've attached a photo for how this might be set up.

One of the benefits to using a report is that the data is live, so the time-frame for what the last 30 days are will update as the week goes on. You can also embed reports in a Dashboard via the Report Widget. 

azaidi102336

Thanks for the kind suggestions, good people. We ended up going with both routes: TODAY() function in a single consolidated sheet (which we're going to keep open as a matter of course,) from where our dashboards populate, and reports which are shown on the same dashboard - corroborating the charts which source their data from the consolidated sheet. Again, that consolidated sheet's going to be kept 'open', in order to get around the daily-refresh limitation /current-functionality of the Today() function.

Andrée Starå

Excellent!

Happy to help!

schillin

Hi Andree,

I have tried setting up automatic requests overnight, where a cell is in fact modified at 2 am to say that the request was submitted to me, but the rest of the sheet does not update. Additionally, I have reports pointed at the sheet, still no update.

I tried a zapier automation to save the current date into the sheet overnight. It saves that date into a cell, but my today formulas still do not refresh.

I tried reaching out to customer service and they told me this can only be done manually. Is there any way to automate this so that the tool is not useless? Thanks.

JLH

Last item in this thread may be helpful: https://community.smartsheet.com/discussion/50261/smartsheet-function-today#latest

PPS Solutions

Hi Schillin, we have an app which deals with this issue and schedules refreshes of the sheets containing the Today() functions at a time and frequency of your choice. It also can refresh sheets in a set order if you wish to take into account cross-project links that may need to be refreshed in a certain order. Drop us a line if you need more info.

Dan, solutions@projectplanservices.co.uk

Andrée Starå

@schillin

Sounds strange! The Today function should update.

I'd be happy to take a look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)