Display Running Total of Days

Options

I have a dashboard that displays various totals, I would like to have in the corner a running total displaying "# of days" since opening day. So when someone opens the dashboard they can easily see Stats for 10 days, then 11 days, then 12 days... so they can then do there own stats... Note while we are in operation 7 days a week, there are days with no activity. A bonus would be do display the current date.

And lastly, a formula that would allow me to set a start day and end day once the season is completed.

Answers

  • Genevieve P.
    Options

    Hi @SkiPatrolScott

    What I would do in this instance is set up a Dashboard Metrics sheet to house my calculations.

    • If you're referencing a project sheet, you can cell link in the Start Date of the project (or manually enter a Date) into a Date column. My column is called "Start Date".
    • Then I would set a Record a Date workflow to run every day at a specific time, recording today's date in a different column ("Today's Date" column). Note that you'd want something on the row to set as the Condition for what rows should record today's date.
    • Finally, I would use a Formula to calculate the difference between Today's Date and the Start Date to find the number of days. (See: Use Formulas to Perform Calculations With Dates)


    Workflow set up example:

    See: Create a Time-Based Automated Workflow

    Now that we have the cells set up in the helper sheet, we can reference them in Metric Widgets on your dashboard. The Workflow will update cells every day, which will update your formula and your Metric Widget. You can reference one of the Today's Date cells for the date, and your Formula cell for the number of days.

    Then if you need to STOP the date at any time, you can change the helper text in the "Workflow Trigger" column that I used as the Condition. You could write something like "Project Complete" so it stops updating the Today column and you have finalized data.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!