Smartsheet Function - TODAY()

DanielGD

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!

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.

Andrée Starå

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

Best,

Andrée

Andrée Starå

Paul,

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

Best,

Andrée

Paul Newcome

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

 

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

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

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.

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.

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!

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. 

Andrée Starå

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

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

Zapier sounds like the better choice. 

Paul Newcome

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.

Andrée Starå

Haha!

It's a lot of pages for anyone!

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

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

@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.