Dashboard Formula for Projects Within Date Range

HProulx

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!

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"?

HProulx

Hi @HProulx<\/a>,<\/p>

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.<\/p>

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.<\/p>

Does this make sense to you?<\/p>

Hope this helps!<\/p>

Stefan<\/p>

PS: You write "due date column" but probably mean "start date"?<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"HProulx\",\"userID\":148822}}},{\"insert\":\",\\nas 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.\\nI 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.\\nDoes this make sense to you?\\nHope this helps!\\nStefan\\nPS: You write \\\"due date column\\\" but probably mean \\\"start date\\\"?\\n\"}]","format":"rich","dateInserted":"2022-06-09T10:24:42+00:00","insertUser":{"userID":122299,"name":"Stefan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Stefan","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nJVa34H1C4Q!HEtZnwWHg7o!c2eUa44FlAn","dateLastActive":"2023-07-29T08:59:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/332393#Comment_332393","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/332393#Comment_332393

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

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([email protected]) 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