Home

Need help with simple timesheet formula

%3CLINGO-SUB%20id%3D%22lingo-sub-371906%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20simple%20timesheet%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-371906%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%2C%20my%20skills%20with%20excel%20are%20limited%2C%20but%20I'm%20sure%20this%20will%20be%20a%20simple%20formula%20for%20most.%20Here's%20what%20I%20have%20and%20what%20I'm%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%26nbsp%3B%20A-E%20all%20have%20a%20value%20of%20%2210%22%2C%20representing%20the%20total%20number%20of%20hours%20worked%20for%20each%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20F%20shows%20that%20total%20of%20hours%20as%20%2250%22%20...%20I'm%20using%20%3Dsum(A1%2CB1%2CC1%2CD1%2CE1)%3C%2FP%3E%3CP%3ECell%20G%20shows%20the%20total%20of%20OT%20hours%20as%20%2210%22%20...%20%3Dif(F1%26gt%3B40%2CF1-40%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20cell%20F%20to%20show%2040%20and%20still%20have%20cell%20G%20to%20show%2010.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-371906%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-374045%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20simple%20timesheet%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-374045%22%20slang%3D%22en-US%22%3EBoth%20suggestions%20work.%20Thanks%20so%20very%20much.%20I'm%20still%20tweaking%20my%20sheet%20to%20best%20suit%20my%20needs%2C%20but%20now%20at%20least%20my%20numbers%20match.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-372755%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20simple%20timesheet%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-372755%22%20slang%3D%22en-US%22%3EF1%3DMIN(SUM(A1%3AE1)%2C40)%3CBR%20%2F%3EG1%3DSUM(A1%3AE1)-F1%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-372165%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20simple%20timesheet%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-372165%22%20slang%3D%22en-US%22%3EIn%20cell%20F%20%3DIF(SUM(A1%3AE1)%26gt%3B40%2C40%2CSUM(A1%3AE1))%20and%20in%20cell%20G%20%3DSUM(A1%3AE1)-F1%20i%20guess%20this%20would%20solve%20your%20issue%20%2C%20if%20not%20please%20explain%20more%20in%20order%20for%20me%20to%20understand%20%3A)%3C%2FLINGO-BODY%3E
SeanC1970
New Contributor

Sorry, my skills with excel are limited, but I'm sure this will be a simple formula for most. Here's what I have and what I'm trying to achieve.

 

Cells  A-E all have a value of "10", representing the total number of hours worked for each day.

 

Cell F shows that total of hours as "50" ... I'm using =sum(A1,B1,C1,D1,E1)

Cell G shows the total of OT hours as "10" ... =if(F1>40,F1-40,"")

 

I want cell F to show 40 and still have cell G to show 10.

3 Replies
In cell F =IF(SUM(A1:E1)>40,40,SUM(A1:E1)) and in cell G =SUM(A1:E1)-F1 i guess this would solve your issue , if not please explain more in order for me to understand :)
F1=MIN(SUM(A1:E1),40)
G1=SUM(A1:E1)-F1
Highlighted
Both suggestions work. Thanks so very much. I'm still tweaking my sheet to best suit my needs, but now at least my numbers match.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies