Department capacity reporting

Hello,

I'm trying to find an effective way to report capacity by department in a dashboard. For example, I have multiple departments working on several projects with different start/end months. What would be the best way to report on this in a dashboard?


Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @anuelle

    The tricky thing here is that your dates are listed as text and not as a Date in a Date Column. This means that there isn't a way to easily check to see if a row is active in "March" if the Start says "February" and the end say "April".

    What I would do is set up two helper date columns next to each of your Start and End text fields. Then you can use a formula to automatically populate the first of the month and year that you have written down.

    Ex:

    =IF([Target Start]@row = "January 2022", DATE(2022, 01, 01), IF([Target Start]@row = "February 2022", DATE(2022, 02, 01), IF([Target Start]@row = "March 2022", DATE(2022, 03, 01)... etc

    See: IF Function / Create and Edit Formulas in Smartsheet / DATE Function


    Once you have actual Dates to reference, I would build a Metric sheet that lists your Departments in a single cell down one column, then Text columns to represent each Month/Year.

    You can set up a COUNTIFS formula to COUNT how many projects each individual Department is working on in each month, by checking to see if that month falls in the date range of the helper Date columns you have set up.

    Ex to look in January:

    =COUNTIFS({Who Column Source Sheet}, HAS(@cell, [Who Column]@row), {Start Date Column}, >= DATE(2022, 01, 01), {End Date Column}, <= DATE(2022, 01, 01))

    This metric sheet can then be used as a source for graphs or charts in your Dashboard. Let me know if this makes sense or if you need help with either formula; I'd be happy to post screen captures as well if that would be useful.

    Cheers,

    Genevieve