Dashboards & Formulas

dwayne.waterman21236

Hello, I have (2) fields on a sheet that are driven by dates.  here is an example:

=if(and(today()>=date(2019,9,1),today()<date(2019,10,1)),"Period 1")

So on the first of each month, this field on my dashboard should update to the current month.  For some reason, the dashboard will only update if the sheet is opened.  is there a way to make this automatic?  it kinda defeats the purpose of utilizing the formula if i have to open the sheet in order for it to execute.  

Mike Wilday

As long as you use the Today() formula you're going to have to open the original sheet every time. That's one of the drawbacks because it has to open the sheet to recalculate today's date. Do you have another sheet that you (or your team) opens every day? If so, you could calculate a =Today() date in there so you have a constantly updating date that your original formula could reference with a cross-sheet formula. 

Would that work? 

Paul Newcome

I use Zapier for this. I have a sheet simply called "TODAY". Each day at 12:01am, zapier adds a new row to this sheet. I just have it doing a very basic time stamp in the Primary column.

 

Column2 of this sheet is a date type column with the following in row 1:

 

=TODAY()

.

All of my other sheets that utilize the TODAY function have a cell link to the TODAY sheet.

.

Zapier adds a new row automatically to my TODAY sheet.

This activates the sheet which in turn updates the TODAY function.

The cell links going out to the other sheets also update automatically.

.

This means that my TODAY functions will update across all sheets even if no one logs on at all.

.

I built a very basic row counter into my TODAY sheet and have a reminder automation set up to let me know when it gets to 4,950.

 

This way I can go in when my TODAY sheet is approaching the 5,000 row limit and delete old rows to make room for new rows.

 

5,000 row limit = more than 13 1/2 years before I need to do any kind of maintenance. Hopefully by then SS will have something in place where this workaround isn't needed.

.

So basically with very little setup and virtually no maintenance, I now have an automatically updating TODAY function on all of my sheets.

.

The other nice thing about this... That's the only thing I run through Zapier which means the number of Zaps that happen per month are well below the mark that requires a paid subscription, so it's a free solution for me as well!!!

Paul Newcome

@Mike:

 

Aside from my below solution, there a few other options as well. You don't have to open the sheet. Only activate it. A cell link that updates from another sheet, a cross sheet reference that causes data to update, a form entry, an update request submission... All of these things can be used to activate a sheet without actually opening it.

 

Once the sheet has been activated, the TODAY reference will update.

.

An additional note to what you mentioned about the cross-sheet formula...

 

I have found that using a single cell link to pull today's date from that sheet to the target sheet is much more efficient that multiple cross-sheet formulas. Once you use the single cell link to pull it to the target sheet, you just reference that one cell in your formulas instead.

 

For this I generally create a date type column named "TODAY" and put the cell link in row 1. Then I can just use $TODAY$1 in my formula in place of the TODAY() function. It is only 1 more keystroke per occurrence, and it avoids the performance burden of multiple cross-sheet references.

Mike Wilday

@Paul Very creative solutions for having an automated today() date being updated. Good use of Zapier as well! 

Paul Newcome

Thanks Mike!

 

It was either that or overtime for logging in on my days off, and the overtime was definitely not approved. Haha.