New Contributor

# 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 A3 hrs - Project B2 hrs - Project C 4 hrs -Project B4 hrs -Project D 8 hrs - Project A 8 hrs - Project B 4.25 hrs - Project C.25 hrs - Project A.75 hrs - Project B2.75 hrs - Project D Tony 8 hrs - Project A 8 hrs - Project A 8 hrs - Project A 8 hrs - Project A 7 hrs - Proejct A1 hr - Overhead Sergio 2 hrs - Project C6 hrs - Project D 8 hrs - Project D 8 hrs - Project C 4 hrs - Project A4 hrs - Project C 3 hrs - Project C5 hrs - Project B 17 hrs - Project C14 hrs - Project D4 hrs - Project A5 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.

4 Replies

# Re: What formula to use here

@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)

# Re: What formula to use here

@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!

# Re: What formula to use here

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.

# Re: What formula to use here

Thanks so much for this, I'll give it a shot!