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

soeirorenan
soeirorenan ✭
edited 01/20/20 in Formulas and Functions

Hi,


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?


2020-01-20_18-33.png

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)


2020-01-20_18-35.png

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

Tags:
· Share on FacebookShare on Twitter

Answers

  • 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).

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • Gordon
    Gordon ✭✭✭✭✭

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!