Forum Discussion
What formula to use here
Hello there, I need help figuring out which formula to use here...
Every day employees send me their hours for that day, which project they were working on, and for how long. At the end of the week, I add up the total hours worked by the project they worked on. Some people are easier than others, but it would be nice to automate that step in the process with a formula. I put an example below of what my chart looks like. Let me know any suggestions for a formula to put in the column labeled "Total". An example of what I am looking for is the formula to do at the end of the column labeled total, and the row labeled Sergio.
( assume this table is in the top left corner of the table and B1 is Monday, A2 is Randy, so on and so forth)
| Monday | Tuesday | Wednesday | Thursday | Friday | Total | |
| Randy | 3 hrs - Project A 3 hrs - Project B 2 hrs - Project C | 4 hrs -Project B 4 hrs -Project D | 8 hrs - Project A | 8 hrs - Project B | 4.25 hrs - Project C .25 hrs - Project A .75 hrs - Project B 2.75 hrs - Project D | |
| Tony | 8 hrs - Project A | 8 hrs - Project A | 8 hrs - Project A | 8 hrs - Project A | 7 hrs - Proejct A 1 hr - Overhead | |
| Sergio | 2 hrs - Project C 6 hrs - Project D | 8 hrs - Project D | 8 hrs - Project C | 4 hrs - Project A 4 hrs - Project C | 3 hrs - Project C 5 hrs - Project B | 17 hrs - Project C 14 hrs - Project D 4 hrs - Project A 5 hrs - Proejct B |
Also, yes we do have a payroll system, but we use this as a step to double their hours are correct and going towards the right project so we know who to bill for their time.
DylanTowbes I'd recommend using PowerQuery to clean up that data first. Then you can easily create pivot tables. One for the hours per employee to check the payroll and another to summarise all hours by project for billing.
Example attached!
- Thanks so much for this, I'll give it a shot!
DylanTowbes I had to make a reference table in the side that you can hide or cut to a different sheet.
basically recognizes time as numbers before "_hr" and project names as characters after "Project_" (underscore Being a space)
- Thank you so much for this, I really enjoy the functionality of it. I am having a hard time tracking some of the formulas haha but that's due to my basic knowledge of formulas.
