Compare the hours allocated to the hours actually worked, through timesheets



I need to compare the hours allocated to the hours actually worked, through timesheets.

For this I am using a spreadsheet to aggregate all the data from timesheets in order to extract metrics of hours worked by time and in which projects. This time worked on projects would be compared to the time allocated to the employee. However, the limit of 5000 lines is being a big problem to me.

Another way to aggregate all these timesheets would be to use reports, however formulas cannot be used in reports and I cannot use your data in formulas in another spreadsheet.

How could you do that without having to create multiple spreadsheets?

Fig1. Project planning. In the "Codigo" column I have an identifier for the activity and in the "Horas Apontadas (h)" column I have a formula that calculates the sum of the employee's appointed hours in the activity.

"Horas Apontadas (h)" formula example: =SUMIFS({database of hours - duration}; {database of hours - member}; Member@row; {database of hours - activity}; $activity$@row)

Fig2. Timesheet. In the "ATIVIDADE" column, the activity identifier must be inserted.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @soeirorenan

    I may not be understanding your project correctly, but it sounds like you have already explored the potential options within the Smartsheet app. Other solutions would likely require additional sheets to be created to work with the 5,000 row limit, but it doesn't sound like you wish to do this.

    Have you heard about 10,000ft? 10,000ft is a Resource Management platform recently acquired by Smartsheet that would be ideal for identifying this type of information and creating these reports. You can read more about this platform in our Help Center (click here) or on the 10,000ft website (click here).



  • Gordon
    Gordon ✭✭✭✭✭

    I have this same issue, but unfortunately, the pricing for 10kft is cost-prohibitive at this point.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!