Home

Help with timesheet formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-331384%22%20slang%3D%22en-US%22%3EHelp%20with%20timesheet%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-331384%22%20slang%3D%22en-US%22%3E%3CP%3EI%20found%20a%20weekly%20time%20sheet%20online%20and%20made%20some%20modifications%20to%20suit%20my%20needs%2C%20however%2C%20there%20are%20many%20more%20modifications%20that%20require%20formulas%20I%20have%20no%20knowledge%20of%20creating.%20You'd%20think%20I%20wouldn't%20have%20to%20create%20all%20this%2C%20but%20my%20company%20has%20the%20knack%20of%20not%20always%20paying%20correctly%2C%20so%20I'm%20inputting%20my%20hours%20for%20comparison.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20my%20company%20DOES%20NOT%20protect%20overtime%20daily%2C%20this%20has%20been%20my%20biggest%20challenge%2C%20not%20to%20mention%20a%20rotating%20schedule%20with%20a%20fixed%20pay%20period.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EFeb.%207%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15%3A50%3C%2FTD%3E%3CTD%3EReg.%20Hrs.%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3A05%3C%2FTD%3E%3CTD%3E10.25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EOT%20Hrs.%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThis%20is%20where%20I'm%20at%20so%20far%20after%20making%20some%20resent%20changes.%20Remember%2C%20most%20of%20this%20formula%20was%20from%20a%20template%20that%20I've%20modified.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(IF((((N1-J96)%2B(J97-O1))*24)%2C((N1-J96)%2B(J97-O1))*24)%2C%20%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELets%20say%20that%20%22J96%22%20is%20my%20start%20time%20of%2015%3A50%20and%20%22J97%22%20is%20my%20end%20time%20of%202%3A05.%20I%20work%20a%20night%20shift%20that%20carries%20over%20to%20the%20next%20day%20so%20%22N1%22%20is%2023%3A59%20and%20%22O1%22%20is%2000%3A00.%20If%20there%20is%20an%20easier%20way%2C%20please%20let%20me%20know.%20Now%20for%20the%20next%20part%20which%20I%20can't%20seem%20to%20figure%20out.%20I'd%20like%20to%20manually%20enter%20my%20lunch%20time%20in%20%22J98%22.%20Let's%20say%2030%20min.%20So%20under%20regular%20hours%20%22K97%22%20it%20would%20show%20%229.75%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20for%20the%20fun%20part%2C%20I%20need%20a%20running%20total%20of%20hours%20%22K98%22%20and%20when%2040%20hours%20has%20been%20reached%2C%20another%20cell%20%22K99%22%20will%20show%20the%20overtime%20hours%20over%2040.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20I%20can%20get%20this%20part%20done%2C%20I%20think%20there%20will%20be%20one%20last%20step%2C%20or%20so%20I%20hope%2C%20that%20will%20complete%20my%20needs.%20I'm%20sure%20all%20you%20gurus%20out%20there%20will%20find%20this%20an%20easy%20task.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20very%20much%20for%20all%20your%20help.%20Let%20me%20know%20if%20you%20require%20additional%20information.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-331384%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
Highlighted
SeanC1970
New Contributor

I found a weekly time sheet online and made some modifications to suit my needs, however, there are many more modifications that require formulas I have no knowledge of creating. You'd think I wouldn't have to create all this, but my company has the knack of not always paying correctly, so I'm inputting my hours for comparison.

 

Because my company DOES NOT protect overtime daily, this has been my biggest challenge, not to mention a rotating schedule with a fixed pay period.

 

Feb. 7
15:50Reg. Hrs.
2:0510.25
 OT Hrs.
  

This is where I'm at so far after making some resent changes. Remember, most of this formula was from a template that I've modified.

 

=IFERROR(IF((((N1-J96)+(J97-O1))*24),((N1-J96)+(J97-O1))*24), "")

 

Lets say that "J96" is my start time of 15:50 and "J97" is my end time of 2:05. I work a night shift that carries over to the next day so "N1" is 23:59 and "O1" is 00:00. If there is an easier way, please let me know. Now for the next part which I can't seem to figure out. I'd like to manually enter my lunch time in "J98". Let's say 30 min. So under regular hours "K97" it would show "9.75"

 

Now for the fun part, I need a running total of hours "K98" and when 40 hours has been reached, another cell "K99" will show the overtime hours over 40.

 

Once I can get this part done, I think there will be one last step, or so I hope, that will complete my needs. I'm sure all you gurus out there will find this an easy task.

 

Thanks so very much for all your help. Let me know if you require additional information.