Smartsheet Function - TODAY()

DanielGD
DanielGD ✭✭✭✭
edited 12/09/19 in Formulas and Functions

I use this function, TODAY(), to calculate days to an event or past said event.

I've noticed that you have to open the sheet to update or probably more accurately get the TODAY function to be active and calculate.

I use this day counts in a Dashboard and to be accurate, as mentioned, I need to open that particular worksheet.

Is there a work-around where the TODAY function or another method where the worksheet doesnot need to be opened?

Thanks!

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The sheet only needs to be made "active". It doesn't necessarily have to be "opened". You can use form entries and update requests to activate the sheet. I keep getting busy and forgetting to test whether or not Automations will "activate" the sheet. I need to remember to do that.

     

    Otherwise... There is no real workaround for the TODAY() function.

     

    I am not positive, but there may be a way to "ping" the sheet with a 3rd party app such as Zapier or through the API, but I am not completely sure.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Yes, Zapier can be set up to do it automatically!

    Best,

    Andrée

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 06/11/19

    Paul,

    Did you get a response regarding the CloudFlare blocking issue? It's strange. I can't see a pattern.

    Best,

    Andrée

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    No response yet. I haven't been able to pick up on a pattern either. Earlier a basic "Happy to help! yes" post was even blocked.

     

    Haha. I once got blocked when I was trying to submit a support ticket for getting blocked. surprise

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is Zapier free, or do they have a free version? It seems like this keeps coming up as a solution that I could definitely use, but my company doesn't like spending money...

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    It's peculiar!

    I got blocked and edited the message a few times to look the same as when it was blocked and that worked.

    indecision

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Whenever I get blocked, I have to change it up at least a little bit before it will go through.

     

    123456 might get blocked and will keep getting blocked no matter how many times I try it, but 12345 6 will go through. It seems random. I still haven't heard back from them about it yet either. I'll definitely keep you posted.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Zapier has a free plan, and it will be enough for the TODAY workflow. Another option could be to have a daily update request sent that you trigger each day.

    Hope that helps!

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Thanks. I'll have to check it out. Unfortunately the update request option wouldn't work for me. I have already built out so much avoiding the TODAY() function, that going back through and taking advantage of being able to use it wouldn't be worth the effort.

     

    My managers had me put together an almost fully automated (95%) workspace for them with all kinds of mess built in. I've been working on it for more than a year (almost 40 hours a week at that), and it still isn't done. I am most definitely not going to go back and do a major rewrite on 163 sheets, reports, and dashboards just for the convenience of the TODAY() function and update requests. Haha.

     

    So... Zapier testing it is. yes

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Have you seen the Neverending Story? This sound like the Neverending Project! wink

    163 sheets are nothing, and you can get that done over a weekend! cool

    Zapier sounds like the better choice. yes

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/18/19

    Neverending Project is right! Just as I was wrapping up all of the little tweaks and links and tiny little details... BAM!

    .

    "Here's a few "minor" changes we need to make. How long will that take you?"

     

    "Ummm... About 3 months..."

     

    "But it's just removing a few lines and adding a few others!!"

     

    "Those "few lines" are part of the single main driving factor of 90% of ALL automations within this entire workspace. That's why we spent so much time making sure these few lines were good to go, and why I said that any changes to these few lines would require a major overhaul."

     

    "But it's just a few lines!"

     

    "Those few lines automatically populate your project plan, which in turn is linked to the departmental rollup. That rollup is used for all of the YTD tracking you asked for which happens to be more than 3,000 points of data for just 6 people going from a very broad executive level dashboard all the way down to individual stats broken down by week, by person for each of the 16 different types of projects, tracking historical data, current data, and projections.

    Not to mention the excessively detailed "build manual" you asked for (that's over 1,000 pages long and only 2/3 of the way done) that is going to need updated to the point of being almost an overhaul in and of itself.

    So yes. 3 months is an optimistic time frame considering I still have the job I was hired to do that you want made a priority over getting all of this set up in SS."

     

    "... Oh... ... ... ... Ok. Well just see what you can do."

    .

    It may not seem like a lot of pages to you, but I was hired to implement new clients. I had never even heard of SS until a little more than a year ago. Now I have SS 40 hours a week on top of my actual job.

     

    At this point... It may be more efficient in the long run to integrate Zapier, and I may start doing that moving forward once my workload lightens up a bit. But right now "If it ain't broke, don't fix it." is going to be the main driving factor in letting the current sheets stay how they are. Haha

     

    Hopefully I'll get a chance to play around in Zapier here in the next few weeks to see if I can make it work how I need it to. I've also been trying to study up on API's, but I swear I feel like I don't even know how to read. Haha

    .

    P.S.

    Sorry for the rant. 2 hours of sleep before coming into a mess at work has me a little irritable. Hahaha.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    surprise

    Haha!

    It's a lot of pages for anyone!

    The phrase, real quick, can you just fix this! is universal! laugh

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

    Can anyone help with how to set up a zap to just refresh cells? I created a zap which added a date into a cell (not a cell that contained my today formula).


    I really need a way to refresh my current today formulas. Can anyone help?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @schillin I use a zap to add a new row to a sheet where I also have a date type column that simply contains

    =TODAY()

    When the zap adds the new row to the sheet, it updates the TODAY() function to the current date. I have it set to add a new row daily at 0000 (or 12:00am).


    Now that I have an updating TODAY function, I can use cell linking to pull this date to my other sheets. I generally drop it in a hidden helper column I call [Today].


    Then in the formulas I can reference $Today$1 instead of TODAY(), and because it is cell linked, it also updates daily even if the sheets are not activated by other means.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!