How do I get a date value to show/change in a dashboard?

rschneider

I have inherited and existing Smartsheet, which I am just learning. The dashboard has a metric widget with a week of, but it does not change we are stuck on one week. I have a column that is now labeled as "Week Of" the cells show the date as 07/28/24 and the column type has been changed to date. How do I go about setting this up so that the date from that week pulls from the source date and if need be I change the date and look at a previous weeks data?

Jason Tarpinian

A metric widget will pull one specific cell from either your grid data, or the Sheet Summary. So if you want the value changing regularly, without interaction, you could build a formula into the cell. If the value is going to be changed manually, then you just open the cell/Sheet Summary and change when you need to.

rschneider

https://community.smartsheet.com/discussion/comment/467549#Comment_467549

So that is getting me closer to what I am needing.

The Week Of data runs a week behind. We want the Week Of to update every Sunday to the data of the week before. Example on Sunday 12/29/2024 I want it to show the data for the week of 12/22/2024 with that date.

We also want to be able to change the Week Of to show data from previous weeks.

Are either of these possible?

Jason Tarpinian

Plug the following formula into a sheet summary field for your "Week of". This will assume the date of two Sundays ago (or one Sunday ago if today is Sunday).

=TODAY() - WEEKDAY(TODAY()) - 6

Definitely. You can make another sheet summary field and just reference the date from the field above to calculate whatever you want. Like in the formula below, I'm calculating how many projects were completed for the week of two Sundays ago.

=COUNTIFS([Date Complete]:[Date Complete], >=[Week of]#, [Date Complete]:[Date Complete], <([Week of]# + 7))

It would also help to build an automation like this as I found out the hard way that formulas with the TODAY() function don't update unless somebody opens the sheet or a change is made on the sheet (this formula forces cells to be changed to today's date, therefore trigger the TODAY functions to update properly).

rschneider

okay - think I am getting closer to what I am looking for.

The dashboard I am working needs to reflect the weekly information for the following while keeping the prior weeks stats in the chart:

Plus 3 other columns.

The picture above: on the dashboard they are 4 different charts. the 3 other columns, 2 are on a pie chart & 1 by itself.

I tried the automation, but think did it wrong because it changed the entire column to today's date. I struggle with the formulas (for me…they are just vague enough to not quite get it…still learning had to teach myself on the fly). Do I put the formula in the date cell or create another column and reference the cell?