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

azaidi102336
edited 12/09/19 in Smartsheet Basics

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

Comments

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 09/09/19

    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
    edited 09/20/19

    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å
    Andrée Starå ✭✭✭✭✭✭

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • schillin
    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.

  • PPS Solutions
    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å
    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)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.