Ressource time/allocation tracking on projects


Hello everyone,

I would like to know if someone has a solution to answer my need for time/allocation tracking on projects :

I have a project plan sheet with several columns : task, assigned to, start date, end date and allocation %

I would like to create a metric sheet that is able to get the information for a specific ressource for each month of the sum of allocation %.

For example I have ressource A that works on task 1 from 09/01/2020 to 11/30/2020 at a 30% allocation. So in my metric I would like to retrieve 30% allocation for septembre, for october and for november.

And if I have multiple tasks on the same month, I would like to have the sum of allocation %.

Do you know if it is possible to get these informations ?

Thank you very much ! Have a nice day



  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Yes, you can get this info. Your cross sheet formula in your metrics would need to read your start and end dates to determine which projects fall into a particular month, than pull out the sum of the allocation % for that month. a SUM(COLLECT()) might work here.

    👨🏼💻Dan Palenchar |School of Sheets Solutions Consulting| Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response attention use@Dan Palencharso I get notified of your reply!

  • Corentin Dal Farra
    Corentin Dal Farra ✭✭✭✭✭✭

    Hello Dan,

    thank you for your answer, could you please explain to me more about the solution you are thinking about ?

    For now I tried to create columns and formulas that breaks down each task duration into related months. So for example for the second task it is only one month duration (15 days exactely), but for the third task it is 3 months .

    And in a metric sheet I sum all the allocation % for each task assigned to a specific ressource.

    It is really complicated though..

    The problem is I only have this metric for one specific project, and I would like to have the same for all y projects.

    Thank you again,