SOLVED
Home

Automatic Addition, Numerical Limit and Overflow

%3CLINGO-SUB%20id%3D%22lingo-sub-797542%22%20slang%3D%22en-US%22%3EAutomatic%20Addition%2C%20Numerical%20Limit%20and%20Overflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797542%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20creating%20an%20overtime%20report%20sheet%20for%20a%20crew%20ranging%20from%2020%20to%2075%20people.%20Rather%20than%20type%20up%20all%20of%20the%20hours%20for%20each%20individual%2C%20highlighting%20everyone's%20times%20and%20then%20using%20the%20sum%20feature%20to%20get%20their%20totals%20into%20column%20L%2C%20I%20would%20much%20prefer%20having%20their%20times%20automatically%20add%20up%20into%20column%20L%20as%20I%20type%20them.%20Is%20this%20possible%3F%3CBR%20%2F%3EAdditionally%2C%20In%20column%20M%2C%20I%20input%20regular%20hours%2C%20so%20even%20if%20someone%20works%2055%2C%20column%20M%20will%20say%2040%20and%20in%20Column%20N%20would%20go%20the%2015%20overtime%20hours.%20Can%20I%20make%20it%20so%20that%20if%20a%20value%20greater%20than%2040%20is%20put%20into%20Column%20M%20the%20remaining%20value%20could%20automatically%20overflow%20onto%20the%20next%20column%3F%3CBR%20%2F%3E%3CBR%20%2F%3EP.S.%20In%20Column%20O%20is%20the%20premium%20time%20which%20is%20just%20half%20of%20the%20overtime%2C%20but%20I%20managed%20to%20automate%20that%20to%20divide%20values%20in%20Column%20N%20by%202.%20This%20is%20all%20I've%20figured%20as%20I%20don't%20have%20much%20Excel%20experience%20and%20am%20unsure%20how%20else%20to%20find%20these%20answers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-797542%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMath%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797574%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Addition%2C%20Numerical%20Limit%20and%20Overflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797574%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389885%22%20target%3D%22_blank%22%3E%40Jrodz500%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20which%20columns%20do%20you%20manually%20input%20the%20employees'%20hours%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797667%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Addition%2C%20Numerical%20Limit%20and%20Overflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797667%22%20slang%3D%22en-US%22%3E%3CP%3EColumns%20E%20-%20K%20represent%20Monday%20to%20Sunday%20and%20the%20first%20row%20that%20has%20time%20in%20it%20is%20row%204.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797726%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Addition%2C%20Numerical%20Limit%20and%20Overflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389885%22%20target%3D%22_blank%22%3E%40Jrodz500%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20easier%20to%20add%20one%20more%20column%20for%20total%20hours.%20If%20you%20type%20in%20it%2C%20let%20say%2C%2055%2C%20in%20next%20column%20will%20be%20regular%20hours%20as%20%3DMIN(total%2C%2040)%2C%20in%20next%20overtime%20as%20%3DMAX(0%2C%20total-40)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797855%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Addition%2C%20Numerical%20Limit%20and%20Overflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797855%22%20slang%3D%22en-US%22%3E%3CP%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%26nbsp%3B%3CBR%20%2F%3ESorry%2C%20I'm%20a%20little%20confused.%20Here%20is%20a%20bit%20more%20information.%3CBR%20%2F%3E%3CBR%20%2F%3EColumn%20A%20is%20just%20comprised%20of%20numbers%20to%20know%20the%20amount%20on%20the%20list.%26nbsp%3B%3CBR%20%2F%3EColumn%20B%20is%20Employee%20Names%3CBR%20%2F%3EColumn%20C%20is%20Employee%20ID%20Numbers%3CBR%20%2F%3EColumn%20D%20is%20their%20trade.%3CBR%20%2F%3EColumns%20E-K%20are%20Monday%20-%20Sunday%3CBR%20%2F%3EColumn%20L%20is%20total%20hours%20for%20the%20week%3CBR%20%2F%3EColumn%20M%20is%20Regular%20hours%26nbsp%3B%20(%3CSPAN%3E%E2%89%A440)%3CBR%20%2F%3EColumn%20N%20is%20Overtime%20hours%20(The%20remainder%20of%20any%20number%20greater%20than%2040%20in%20column%20M)%3CBR%20%2F%3EColumn%20O%20is%20Premium%20time%20(1%2F2%20of%20Column%20N%20values)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797877%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Addition%2C%20Numerical%20Limit%20and%20Overflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389885%22%20target%3D%22_blank%22%3E%40Jrodz500%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20this%20is%20what%26nbsp%3B%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%26nbsp%3Bis%20suggesting...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EAssuming%20you%20have%20headers%20in%20row%201%20and%20your%20data%20begins%20in%20row%202%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3EIn%20cell%20M2%2C%20you%20may%20put%20the%20formula%3A%3C%2FP%3E%3CP%3E%3DMin(L2%2C40)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20N2%2C%20you%20may%20put%20the%20formula%3A%3C%2FP%3E%3CP%3E%3DMax(0%2CM2-40)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20O2%2C%20you%20may%20put%20the%20formula%3A%3C%2FP%3E%3CP%3E%3DN2%2F2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20of%20course%20copy%20this%20formula%20down%20as%20necessary.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1075009%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Addition%2C%20Numerical%20Limit%20and%20Overflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1075009%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20the%20same%20thing%20but%20i%20do%20not%20understand%20the%20formula%20to%20insert%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162638i0AD5EC4486381496%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%224D1F44DA-05F3-4804-9AE9-6E00777FC3DD.png%22%20title%3D%224D1F44DA-05F3-4804-9AE9-6E00777FC3DD.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1076792%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Addition%2C%20Numerical%20Limit%20and%20Overflow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1076792%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494221%22%20target%3D%22_blank%22%3E%40elliot88%3C%2FA%3E%26nbsp%3BSorry%2C%20from%20this%20screenshort%20it's%20not%20clear%20what%20you'd%20like%20to%20do.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Jrodz500
New Contributor

I am creating an overtime report sheet for a crew ranging from 20 to 75 people. Rather than type up all of the hours for each individual, highlighting everyone's times and then using the sum feature to get their totals into column L, I would much prefer having their times automatically add up into column L as I type them. Is this possible?
Additionally, In column M, I input regular hours, so even if someone works 55, column M will say 40 and in Column N would go the 15 overtime hours. Can I make it so that if a value greater than 40 is put into Column M the remaining value could automatically overflow onto the next column?

P.S. In Column O is the premium time which is just half of the overtime, but I managed to automate that to divide values in Column N by 2. This is all I've figured as I don't have much Excel experience and am unsure how else to find these answers.

7 Replies
Highlighted

Hello @Jrodz500,

 

In which columns do you manually input the employees' hours?

Highlighted

Columns E - K represent Monday to Sunday and the first row that has time in it is row 4.

 

Highlighted

@Jrodz500 

It's easier to add one more column for total hours. If you type in it, let say, 55, in next column will be regular hours as =MIN(total, 40), in next overtime as =MAX(0, total-40)

Highlighted

@Sergei Baklan 
Sorry, I'm a little confused. Here is a bit more information.

Column A is just comprised of numbers to know the amount on the list. 
Column B is Employee Names
Column C is Employee ID Numbers
Column D is their trade.
Columns E-K are Monday - Sunday
Column L is total hours for the week
Column M is Regular hours  (≤40)
Column N is Overtime hours (The remainder of any number greater than 40 in column M)
Column O is Premium time (1/2 of Column N values)

Highlighted
Solution

@Jrodz500,

 

I believe this is what @Sergei Baklan is suggesting...

 

Assuming you have headers in row 1 and your data begins in row 2:

In cell M2, you may put the formula:

=Min(L2,40)

 

In cell N2, you may put the formula:

=Max(0,M2-40)

 

In cell O2, you may put the formula:

=N2/2

 

Then of course copy this formula down as necessary.

Highlighted

I am trying the same thing but i do not understand the formula to insert4D1F44DA-05F3-4804-9AE9-6E00777FC3DD.png

Highlighted

@elliot88 Sorry, from this screenshort it's not clear what you'd like to do.

Related Conversations
Number of paragraphs is incorrect and growing
Tony Godfrey in Sway on
1 Replies