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

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))

SmartSheet forum question.png

Find more posts tagged with

Sheets

Calendar View

Cell linking

Comments

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.

Report without Today.png

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.

AZ Dashboard 2019-0919-B-cleaned.png

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)

Sign in to join the conversation!

Quick Links

Trending discussions

Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
I have a Pro plan but I cannot create ANY reports
I have a Pro plan but I cannot create ANY reports. The system does not allow me to choose a folder, and consequently a sheet to create a row report and suggests an upgrade to Business or Enterprise. I'm quite new to Smartsheet and feel a bit disappointed with this limitation, or maybe I'm just doing something wrong. Alberto