SOLVED

Calculating overtime and double time

%3CLINGO-SUB%20id%3D%22lingo-sub-3042185%22%20slang%3D%22en-US%22%3ECalculating%20overtime%20and%20double%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3042185%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day.%20I%20am%20struggling%20with%20a%20part%20of%20my%20personal%20time%20sheet%2C%20and%20hope%20that%20some%20one%20can%20help%20me%20out%20with%20it.%3C%2FP%3E%3CP%3EI%20am%20using%20the%20office%20suite%20that%20is%20accessible%20to%20me%20on%20my%20hotmail%20account.%3C%2FP%3E%3CTABLE%20width%3D%22801%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%20height%3D%2220%22%3ETime%20in%3C%2FTD%3E%3CTD%20width%3D%22129%22%3ETime%20out%3C%2FTD%3E%3CTD%20width%3D%2293%22%3Ehours%20worked%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Eregular%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EO.T.%3C%2FTD%3E%3CTD%20width%3D%2286%22%3EDouble%20Time%3C%2FTD%3E%3CTD%20width%3D%2276%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2274%22%3Estandard%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Eo.t.%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E03%3A20%3A00%20PM%3C%2FTD%3E%3CTD%3E03%3A20%3A00%20AM%3C%2FTD%3E%3CTD%3E12%3A00%3C%2FTD%3E%3CTD%3E8%3A00%3C%2FTD%3E%3CTD%3E4%3A00%3C%2FTD%3E%3CTD%3E1%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8%3A00%3C%2FTD%3E%3CTD%3E3%3A00%3C%2FTD%3E%3CTD%3E11%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E05%3A50%3A00%20AM%3C%2FTD%3E%3CTD%3E05%3A50%3A00%20PM%3C%2FTD%3E%3CTD%3E12%3A00%3C%2FTD%3E%3CTD%3E8%3A00%3C%2FTD%3E%3CTD%3E4%3A00%3C%2FTD%3E%3CTD%3E1%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EA2%20and%20B2%20are%20simple%20time%20in%20and%20time%20out.%3C%2FP%3E%3CP%3EC2%20is%20total%20hours%20worked%20and%20D2%20is%20regular%20hours%20worked.%20I%20have%20this%20part%20figured%20out.%20My%20problems%20arrive%20when%20I%20get%20to%20E2%2C%20where%20I%20kind%20of%20have%20it%2C%20but%2C%20the%20total%20in%20E2%20should%20not%20be%20more%20than%203%20hours%2C%20and%2C%20anything%20over%20that%203%20hours%20should%20go%20into%20F2.%3C%2FP%3E%3CP%3EC2%20and%20C3%20formulas%20%7B%3CSPAN%3E%3DIF(B55%3CA55%3E%3C%2FA55%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ED2%20and%20D3%20formulas%20%7B%3DIF(C55%26gt%3B%24K%2455%2C%24K%2455%2CC55)%7D%20where%20K55%20%3D%208%3A00%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EE2%20and%20E3%20formulas%20%7B%3DMAX(L55%2CIF(C55%26gt%3BD55%2C(C55-D55)))%7D%20where%20L55%20%3D%203%3A00%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EF2%20and%20F3%20formulas%20%7B%3DIF(C55%26gt%3B%24M%2455%2CC55-%24M%2455)%7D%20where%20M55%20%3D%2011%3A00%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E*The%20%7Bcurly%20brackets%7D%20are%20not%20in%20the%20actual%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3042185%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3042811%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20overtime%20and%20double%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3042811%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3CBR%20%2F%3EYes%2C%20it%20is%20working%20good%20now.%3CBR%20%2F%3EThank%20you%20for%20your%20time.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3042795%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20overtime%20and%20double%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3042795%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1255077%22%20target%3D%22_blank%22%3E%40BrianN68%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20it%20works%20now%20and%20there%20are%20no%20open%20questions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3042758%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20overtime%20and%20double%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3042758%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%3CBR%20%2F%3ESorry%20for%20the%20confusion%20on%20that.%20I%20thought%20that%20I%20changed%20it%20all%20to%20row%202.%3CBR%20%2F%3EThe%20B55%2C%20C55%20etc%20is%20where%20I%20had%20these%20cells%20in%20my%20spreadsheet.%3CBR%20%2F%3EFor%20D2%2C%20that%20would%20be%20my%20straight%20time.%20To%20show%208%20hours%2C%20the%20next%203%20hours%20go%20to%20E2%2C%20then%2C%20anything%20over%2011%20hours%20goes%20to%20double%20time.%3CBR%20%2F%3EBut%2C%20with%20your%20help%2C%20I%20have%20it%20working%20now.%3CBR%20%2F%3EThank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3042543%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20overtime%20and%20double%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3042543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1255077%22%20target%3D%22_blank%22%3E%40BrianN68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome.%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%20what%20do%20you%20have%20in%20C55%2C%20L55%2C%20etc.%20As%20for%20D2%2C%20max%20of%208%20and%20what%20%3F%20Perhaps%20you%20may%20give%20bit%20more%20details.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3042213%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20overtime%20and%20double%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3042213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EEdited%3A%26nbsp%3B%3C%2FSTRONG%3EI%20think%20I%20have%20it.%20I%20just%20put%208%2F24%20in%20place%20of%20the%20%24K%2455.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorks%20like%20a%20charm.%20Thank%20you%20for%20your%20time%20on%20this.%3CBR%20%2F%3EIf%20I%20could%20though%2C%20ask%20one%20more%20question%20on%20this.%3CBR%20%2F%3EIs%20there%20a%20way%20on%20cell%20D2%20to%20have%20it%20max%20at%208%20hrs%20without%20having%20to%20refer%20to%20cell%20K55%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3042197%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20overtime%20and%20double%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3042197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1255077%22%20target%3D%22_blank%22%3E%40BrianN68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EC2%3A%0A%3DMOD(B2-A2%2C1)%0A%0AE2%3A%0A%3DIF(C2%20%26gt%3B%20D2%2C%20MIN(%20C2%20-%20D2%2C%203%2F24)%2C%200%20)%0A%0AF2%3A%0A%3DIF(%20C2%20%26gt%3B%20(D2%20%2B%203%2F24)%2C%20C2%20-%20D2%20-%203%2F24%2C%200%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good day. I am struggling with a part of my personal time sheet, and hope that some one can help me out with it.

I am using the office suite that is accessible to me on my hotmail account.

Time inTime outhours workedregularO.T.Double Time standardo.t. 
03:20:00 PM03:20:00 AM12:008:004:001:00 8:003:0011:00
05:50:00 AM05:50:00 PM12:008:004:001:00   

 

 

A2 and B2 are simple time in and time out.

C2 is total hours worked and D2 is regular hours worked. I have this part figured out. My problems arrive when I get to E2, where I kind of have it, but, the total in E2 should not be more than 3 hours, and, anything over that 3 hours should go into F2.

C2 and C3 formulas {=IF(B55<A55,B55+1,B55)-A55}

D2 and D3 formulas {=IF(C55>$K$55,$K$55,C55)} where K55 = 8:00

E2 and E3 formulas {=MAX(L55,IF(C55>D55,(C55-D55)))} where L55 = 3:00

F2 and F3 formulas {=IF(C55>$M$55,C55-$M$55)} where M55 = 11:00

*The {curly brackets} are not in the actual formulas.

 

Thank you for any help.

7 Replies
best response confirmed by BrianN68 (Occasional Contributor)
Solution

@BrianN68 

It could be

C2:
=MOD(B2-A2,1)

E2:
=IF(C2 > D2, MIN( C2 - D2, 3/24), 0 )

F2:
=IF( C2 > (D2 + 3/24), C2 - D2 - 3/24, 0 )

Edited: I think I have it. I just put 8/24 in place of the $K$55.

 

Works like a charm. Thank you for your time on this.
If I could though, ask one more question on this.
Is there a way on cell D2 to have it max at 8 hrs without having to refer to cell K55?

@BrianN68 

You are welcome.

I'm not sure what do you have in C55, L55, etc. As for D2, max of 8 and what ? Perhaps you may give bit more details.

@Sergei Baklan.
Sorry for the confusion on that. I thought that I changed it all to row 2.
The B55, C55 etc is where I had these cells in my spreadsheet.
For D2, that would be my straight time. To show 8 hours, the next 3 hours go to E2, then, anything over 11 hours goes to double time.
But, with your help, I have it working now.
Thank you.

@BrianN68 , you are welcome.

 

So, it works now and there are no open questions?

@Sergei Baklan
Yes, it is working good now.
Thank you for your time.

I have a question....
I already have a sheet with different fields and my OT column is working, but I need a formula for DT.
Anything over 4 hrs in the G column (OT) needs to go to H column (DT), but G column needs to stay at 4

Start Finish Total Job Description Reg OT DT
8:00 AM 9:45 PM 13.75 At office. 8 4 1.75

C: =(MOD(C12-B12,1)*24)
E: =(D12)-G12
F: =IF((MOD(C12-B12,1)*24)>8,(MOD(C12-B12,1)*24)-8,0)
G: what should this be...?

Or what should I change all my formulas to?