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
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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies