Dashboard Formula for Projects Within Date Range

Hi there, I'm trying to add a widget to my dashboard that tracks how many projects were initiated by year. In the dashboard metrics sheet, I reference the project intake sheet but cannot get the correct formula to count the number of projects by year. For reference, on the project intake sheet, the due date column has the full date (mm/dd/yy). I could be doing this completely wrong but thinking if anyone has experiencing creating a similar formula while referencing another sheet, perhaps you have some insight.

Thanks so much!

Answers

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi @HProulx,

    as you are trying to calculate something for the widget and widgets only show data, you need to provide the desired info in a sheet or report.

    I suggest setting up a metrics sheet or some additional columns in the intake sheet where you count projects started by year and then use this as source for the widget.

    Does this make sense to you?

    Hope this helps!

    Stefan

    PS: You write "due date column" but probably mean "start date"?

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • HProulx
    HProulx ✭✭

    Hi there, yes I have the metrics sheet set up and all my other formulas work great just can't figure out which one to use for the tracking the jobs by year.

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi @HProulx,

    have a look at the screenshot and the formula. For your location you may need to exchange ";" by "," in the formula !

    Instead of the reference to the columns in the metrics sheet use cross sheet references to the intake sheets columns. The "start year" column contains =year(start@row) and can be hidden. The yellow coloured cells can be used as source for your dashboard widget.

    Somewhat quick and dirty but should work for you; I hope.

    Greetings

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!