What formula to use here

%3CLINGO-SUB%20id%3D%22lingo-sub-3479038%22%20slang%3D%22en-US%22%3EWhat%20formula%20to%20use%20here%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3479038%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20there%2C%20I%20need%20help%20figuring%20out%20which%20formula%20to%20use%20here...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEvery%20day%20employees%20send%20me%20their%20hours%20for%20that%20day%2C%20which%20project%20they%20were%20working%20on%2C%20and%20for%20how%20long.%20At%20the%20end%20of%20the%20week%2C%20I%20add%20up%20the%20total%20hours%20worked%20by%20the%20project%20they%20worked%20on.%20Some%20people%20are%20easier%20than%20others%2C%20but%20it%20would%20be%20nice%20to%20automate%20that%20step%20in%20the%20process%20with%20a%20formula.%20I%20put%20an%20example%20below%20of%20what%20my%20chart%20looks%20like.%20Let%20me%20know%20any%20suggestions%20for%20a%20formula%20to%20put%20in%20the%20column%20labeled%20%22Total%22.%20An%20example%20of%20what%20I%20am%20looking%20for%20is%20the%20formula%20to%20do%20at%20the%20end%20of%20the%20column%20labeled%20total%2C%20and%20the%20row%20labeled%20Sergio.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(%20assume%20this%20table%20is%20in%20the%20top%20left%20corner%20of%20the%20table%20and%20B1%20is%20Monday%2C%20A2%20is%20Randy%2C%20so%20on%20and%20so%20forth)%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2299.78295185477506%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%228.56353591160221%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2218.23204419889503%25%22%3EMonday%3C%2FTD%3E%3CTD%20width%3D%2215.193370165745856%25%22%3ETuesday%3C%2FTD%3E%3CTD%20width%3D%2214.088397790055248%25%22%3EWednesday%3C%2FTD%3E%3CTD%20width%3D%2213.397790055248619%25%22%3EThursday%3C%2FTD%3E%3CTD%20width%3D%2216.022099447513813%25%22%3EFriday%3C%2FTD%3E%3CTD%20width%3D%2214.285714285714286%25%22%3ETotal%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%228.56353591160221%25%22%3ERandy%3C%2FTD%3E%3CTD%20width%3D%2218.23204419889503%25%22%3E3%20hrs%20-%20Project%20A%3CBR%20%2F%3E3%20hrs%20-%20Project%20B%3CBR%20%2F%3E2%20hrs%20-%20Project%20C%3C%2FTD%3E%3CTD%20width%3D%2215.193370165745856%25%22%3E%26nbsp%3B4%20hrs%20-Project%20B%3CBR%20%2F%3E4%20hrs%20-Project%20D%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.088397790055248%25%22%3E8%20hrs%20-%20Project%20A%3C%2FTD%3E%3CTD%20width%3D%2213.397790055248619%25%22%3E8%20hrs%20-%20Project%20B%3C%2FTD%3E%3CTD%20width%3D%2216.022099447513813%25%22%3E4.25%20hrs%20-%20Project%20C%3CBR%20%2F%3E.25%20hrs%20-%20Project%20A%3CBR%20%2F%3E.75%20hrs%20-%20Project%20B%3CBR%20%2F%3E2.75%20hrs%20-%20Project%20D%3C%2FTD%3E%3CTD%20width%3D%2214.285714285714286%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%228.56353591160221%25%22%3ETony%3C%2FTD%3E%3CTD%20width%3D%2218.23204419889503%25%22%3E8%20hrs%20-%20Project%20A%3C%2FTD%3E%3CTD%20width%3D%2215.193370165745856%25%22%3E8%20hrs%20-%20Project%20A%3C%2FTD%3E%3CTD%20width%3D%2214.088397790055248%25%22%3E8%20hrs%20-%20Project%20A%3C%2FTD%3E%3CTD%20width%3D%2213.397790055248619%25%22%3E8%20hrs%20-%20Project%20A%3C%2FTD%3E%3CTD%20width%3D%2216.022099447513813%25%22%3E7%20hrs%20-%20Proejct%20A%3CBR%20%2F%3E1%20hr%20-%20Overhead%3C%2FTD%3E%3CTD%20width%3D%2214.285714285714286%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%228.56353591160221%25%22%3ESergio%3C%2FTD%3E%3CTD%20width%3D%2218.23204419889503%25%22%3E2%20hrs%20-%20Project%20C%3CBR%20%2F%3E6%20hrs%20-%20Project%20D%3C%2FTD%3E%3CTD%20width%3D%2215.193370165745856%25%22%3E8%20hrs%20-%20Project%20D%3C%2FTD%3E%3CTD%20width%3D%2214.088397790055248%25%22%3E8%20hrs%20-%20Project%20C%3C%2FTD%3E%3CTD%20width%3D%2213.397790055248619%25%22%3E4%20hrs%20-%20Project%20A%3CBR%20%2F%3E4%20hrs%20-%20Project%20C%3C%2FTD%3E%3CTD%20width%3D%2216.022099447513813%25%22%3E3%20hrs%20-%20Project%20C%3CBR%20%2F%3E5%20hrs%20-%20Project%20B%3C%2FTD%3E%3CTD%20width%3D%2214.285714285714286%25%22%3E%3CP%3E17%20hrs%20-%20Project%20C%3C%2FP%3E%3CP%3E14%20hrs%20-%20Project%20D%3C%2FP%3E%3CP%3E4%20hrs%20-%20Project%20A%3C%2FP%3E%3CP%3E5%20hrs%20-%20Proejct%20B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3BAlso%2C%20yes%20we%20do%20have%20a%20payroll%20system%2C%20but%20we%20use%20this%20as%20a%20step%20to%20double%20their%20hours%20are%20correct%20and%20going%20towards%20the%20right%20project%20so%20we%20know%20who%20to%20bill%20for%20their%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3479038%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3480536%22%20slang%3D%22en-US%22%3ERe%3A%20What%20formula%20to%20use%20here%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3480536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1416748%22%20target%3D%22_blank%22%3E%40DylanTowbes%3C%2FA%3E%26nbsp%3B%20I%20had%20to%20make%20a%20reference%20table%20in%20the%20side%20that%20you%20can%20hide%20or%20cut%20to%20a%20different%20sheet.%3C%2FP%3E%3CP%3Ebasically%20recognizes%20time%20as%20numbers%20before%20%22_hr%22%20and%20project%20names%20as%20characters%20after%20%22Project_%22%20(underscore%20Being%20a%20space)%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3480771%22%20slang%3D%22en-US%22%3ERe%3A%20What%20formula%20to%20use%20here%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3480771%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1416748%22%20target%3D%22_blank%22%3E%40DylanTowbes%3C%2FA%3E%26nbsp%3BI'd%20recommend%20using%20PowerQuery%20to%20clean%20up%20that%20data%20first.%20Then%20you%20can%20easily%20create%20pivot%20tables.%20One%20for%20the%20hours%20per%20employee%20to%20check%20the%20payroll%20and%20another%20to%20summarise%20all%20hours%20by%20project%20for%20billing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20attached!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3511144%22%20slang%3D%22en-US%22%3ERe%3A%20What%20formula%20to%20use%20here%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3511144%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20this%2C%20I%20really%20enjoy%20the%20functionality%20of%20it.%20I%20am%20having%20a%20hard%20time%20tracking%20some%20of%20the%20formulas%20haha%20but%20that's%20due%20to%20my%20basic%20knowledge%20of%20formulas.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3511150%22%20slang%3D%22en-US%22%3ERe%3A%20What%20formula%20to%20use%20here%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3511150%22%20slang%3D%22en-US%22%3EThanks%20so%20much%20for%20this%2C%20I'll%20give%20it%20a%20shot!%3C%2FLINGO-BODY%3E
New Contributor

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)

 MondayTuesdayWednesdayThursdayFridayTotal
Randy3 hrs - Project A
3 hrs - Project B
2 hrs - Project C
 4 hrs -Project B
4 hrs -Project D 
8 hrs - Project A8 hrs - Project B4.25 hrs - Project C
.25 hrs - Project A
.75 hrs - Project B
2.75 hrs - Project D
 
Tony8 hrs - Project A8 hrs - Project A8 hrs - Project A8 hrs - Project A7 hrs - Proejct A
1 hr - Overhead
 
Sergio2 hrs - Project C
6 hrs - Project D
8 hrs - Project D8 hrs - Project C4 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.

4 Replies

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

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

 

 

 

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.
Thanks so much for this, I'll give it a shot!