Daily Update Solution without the need to open the sheet, use Zapier, or similar

Andrée Starå

Hi everyone,

Happy New Year!

I hope you're well and safe!

I've developed a way to use the newly released Record a date feature to update sheets with today's date automatically without the need to open it or similar.

1. Add a so-called helper column. (Date). We can call it Today Helper and add a date before the current day.

2. You'd then use an Automation Workflow as described below.

Trigger: When a date is reached

Every day starting on 2021-01-06 (today or earlier)

02:00 (select the time that makes the most sense for the process)

Conditions

Where Today Helper is in the past

Record a date

Record a date in Today Helper

This could also be structured so you'd use a so-called helper sheet for the process above and then link it to all other sheets where you'd need the daily update.

Let me know if you have any questions!

I hope that helps!

Stay safe, and Happy New Year!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as helpful. It will make it easier for others to find a solution.

Gavin Worsley

Hello @Andrée Starå

I came to this post when trying to find out how to record the daily results of the SALES KPI's from the team to see trends, i.e. how full the sections of the pipeline are on a weekly basis. Each section of the pipeline has several leads and their values in. This totals in the summary page, report and dashboard. I need to record weekly to see if the trend is up or down per section of pipeline.

I still can't work out how it is possible with the above suggestion. can you advise where i should look for help or any ideas?

Andrée Starå

Hi @Gavin Worsley

I hope you're well and safe!

You could use a copy row workflow to store each week's results and use that for the trend.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Kat Woodham

@Andrée Starå this is similar to what I sorted out for myself as well except instead of a helper column, I use a locked helper row (assume I already have a Date column as part of my setup).

I'm curious about the helper sheet method you mentioned. Do you think it would work to run the automation in the helper sheet and cell link the helper sheet date in the sheet summary of the sheets you need to force a TODAY() formula to update?

Andrée Starå

Hi @Kat Woodham

I hope you're well and safe!

I stumbled over your question.

Unfortunately, you can't cell-link to the sheet-summary section, but what I've done is that I've added a cross-sheet formula instead to the sheet summary section that references, for example, a helper sheet or an Intake Sheet.

I used this method with Zapier when the Record a Date feature wasn't released.

Make sense?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jim Hook

The Post from Andree Stara solved my problem, thank you! I ended up using two automated workflows to generate an email to myself if a number brought into a cell via a cross sheet referenced function exceeded a certain value that would trigger an invoice. The first workflow used the Record a Date action to enter a value into a cell that will trigger the workflows and the second workflow sends an alert email to myself indicating what project has reached an invoice point.

I suspect that the Change Value in Cell action will work also. Both of these actions require the Smartsheet server to open the sheet, write a value into a cell and then save the sheet which triggers the workflows without me ever having to open the sheet myself.

Andrée Starå

@Jim Hook

Excellent!

Happy to help!

Glad to hear that it was helpful!

Paul Newcome

@Andrée Starå I use the same method. It really changed the process when the Record A Date automation came out and we didn't have to use Zapier for this anymore.

mvlukas

Hello @Andrée Starå

In about 30 project plan sheets, I use conditional formatting that uses the "today()" and compares dates to see if they are "in the past". I noticed that the colors on my dashboards did not look right and the conditional formatting seems to not work correctly. I then came across your post.

I created one sheet "Date Helper" and auto workflow as you stated above. I then (on about 30 project sheets) use a "vlookup" in a new field that I added to the 30 sheets in the "Sheet Summary", not a column. I did not want to add a column to the sheet, I would rather like to use the "Sheet Summary" section. The 30 "vlookup" formulas in the Sheet Summaries all look at the one master sheet called "Date Helper".

I ran a test last 2 nights and it seems to be working correctly. Does my solution make sense to you?

Thanks for your help.

Andrée Starå

Hi @mvlukas

I hope you're well and safe!

That is an excellent solution, and I've done the same setup in some of my older client solutions.

It's a matter of preference, so if it works for you and you find it more straightforward than adding a Workflow to each sheet, it sounds like the perfect solution.

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

GarryD2

This is a great way to get a current date in a column without having to open the sheet to trigger the TODAY function. One additional note: if the column does not have a date populated yet, then it will not work. I added to this workflow, if Todays Date Column Blank, then record a date OTHERWISE if Todays Date Column In the past, then record a date.

Andrée Starå

@GarryD2 Thanks!

Paul Newcome

@GarryD2 I do almost the same thing. I just set my conditions to be when the helper column is blank OR when the helper column is not blank. Really all you need is a single cell anywhere in the sheet to update though, so as long as you have at least one row in the sheet that is recording the date, you don't need it on every row.

GarryD2

Thanks and agree, however, we do a calc per row to set RYG and if overdue, we thought it was easier to populate the whole column for that and then do an @row calc that is a column formula. All this is on a Gantt that is calculating against start date and if that line/task is complete or not and what today's date is.

By using this workflow, no longer need Zapier to trigger the days date. Without having the days date on all projects then our program level dashboard was off on all the RYG calcs....

Hope that explains a little more. Thanks

Paul Newcome

@GarryD2 Yes. I think I understand.

Your automation is populating today's date in (for example) the date column called "Today's Date".

In your formulas you use a cell reference as opposed to the TODAY function.

=IF(Date@row = [Today's Date]@row, ......)

as opposed to

IF(Date@row = TODAY(), .....)

I was just pointing out a couple of differences. The conditions in the automation are simply personal preference.

The bit about only needing one row to update from the automation to force all TODAY functions in the sheet to update were because I actually used to set it up that way but then started getting complaints when things were errored out or missing because the [Today's Date] column hadn't been updated from the automation yet immediately after they created a new row. Using the TODAY function avoided that and still works being updated by the automation as long as at least one cell in the [Today's Date] column gets updated. Just a minor pain point that I have run into in the past.

John C Murray

Just a note on the date-based helper columns that are updated via automation to refresh calculated cells - they can only ever work once a day, regardless of how many times you try to trigger them.

This is because Smartsheet compares the existing value of a cell with the value trying to replace it with. If they are identical then Smartsheet assumes that no change has been made, and formula-based cells in the sheet will therefore not be updated. At this point you may choose to scream hysterically at the computer in the manner of a toddler who has overdosed on red cordial. Passing out from hyperventilation is optional.

If you wish to update data on (say) an hourly basis you will have to add a [Modified Date] column to your sheet. The [Modified Date] cell type is a true date/time value. Each of the hourly automations will need to include a condition that checks if the [Modified Date] column is "in the past". You cannot use that condition on any [Date] column if you want to update formula results more than once a day because [Date] cell types do not contain a time component.