Time Sheet Question:

[desnyder2001]

So please be kind, I am a rookie and don't understand a lot of the language people use when responding to my questions.
1. I have a spreadsheet and I DON'T want to reconfigure the whole thing so I am hoping to get an answer to what I hope is a simple question.
2. I am using a customer format for time [h]:mm
3. The spreadsheet is a timesheet.

In cell O7 I sum up 7 days M-F of time totals (= E9+F9+G9+H9+I9+J9+K9)
I only what cell O7 to show any regular hours (40 hours or less)
In cell P7 I would like to display the Overtime Hours / Just the number of hours that are greater than 40.

I seems like it should be simple, but it is NOT Help Please

 

[Myrko]

Hi,
I will assume that you allow 8 regular working hours daily as well:

O7: =min(40,sum(if(E9:K9<=8,E9:K9,8))) execute with Ctrl+Shift+Enter
P7: =sum(if(E9:K9>8,E9:K9-8,0))+if(sum(if(E9:K9<=8,E9:K9,8))>40,sum(if(E9:K9<=8,E9:K9,8))-40,0) execute with Ctrl+Shift+Enter

This will allow up to 8 regular hours a day, 40 a week. Any hour over those limits, no matter where they are, will end up in overtime.

 

[Mackers]

Hello

In Excel, 24 hours = the number 1 (as it stands for 1 day). So if you want to test against 40 hours, you want to test against the number 40/24 (or 1.66666...).

Try these formulas

1. =MIN(O7, 40/24)
2. =MAX(0, O7-(40/24))

 

[Myrko]

Overseen that the data is stored in h:mm format. Here is the corrected version of my initial formula. Sorry for the inconvenience.

O7: =min(40/24,sum(if(E9:K9<=8/24,E9:K9,8/24))) execute with Ctrl+Shift+Enter
P7: =sum(if(E9:K9>8/24,E9:K9-(8/24),0))+if(sum(if(E9:K9<=8/24,E9:K9,8/24))>40/24,sum(if(E9:K9<=8/24,E9:K9,8/24))-(40/24),0) execute with Ctrl+Shift+Enter

Thanks Mackers for bringing this to my attention.

 

[Dave Patton]

The formula that you requires depends on whether or not OT is after 8 hours per day.
You can use the array formula or the sumproduct.

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N
2				Regular	40:00	8:00
3
4											Total	Regular	OT
5	OT after 40 hrs			9:00	4:00	10:00	10:00	10:00			43:00	40:00	3:00
6	OT after 40 hs/week and/or 8 hrs day			9:00	4:00	10:00	10:00	10:00			43:00	36:00	7:00
7	OT after 40 hs/week and/or 8 hrs day			9:00	4:00	10:00	10:00	10:00			43:00	36:00	7:00
8
9	OT after 40 hrs			10:00	10:00	10:00					30:00	30:00	0:00
10	OT after 40 hs/week and/or 8 hrs day			10:00	10:00	10:00					30:00	24:00	6:00
11	OT after 40 hs/week and/or 8 hrs day			10:00	10:00	10:00					30:00	24:00	6:00
12
13	OT after 40 hrs			8:00	8:00	8:00	8:00	8:00	8:00	8:00	56:00	40:00	16:00
14	OT after 40 hs/week and/or 8 hrs day			8:00	8:00	8:00	8:00	8:00	8:00	8:00	56:00	40:00	16:00
15	OT after 40 hs/week and/or 8 hrs day			8:00	8:00	8:00	8:00	8:00	8:00	8:00	56:00	40:00	16:00
16
4c

Cell Formulas
Range	Formula
L5	=SUM(E5:K5)
L6	=SUM(E6:K6)
L7	=SUM(E7:K7)
M5	=MIN($F$2,L5)
M7	=MIN($F$2,L7-SUMPRODUCT(--(E7:K7>$G$2),(E7:K7-$G$2)))
M6	{=MIN($F$2,SUM(IF(E6:K6<=$G$2,E6:K6,$G$2)))}
N5	=L5-M5
N6	=L6-M6
N7	=L7-M7
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[desnyder2001]

Thanks for the quick reply: It gave me an error #Value !
Also, I am not sure what you mean by execute with Ctrl+Shift+Enter. I did press that in the box and still nothing

 

[Dave Patton]

Array formula must be Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

You can use SumProduct or an Array Formula.

Did you provide a complete definition of overtime?
Does OT applies after say 8 hours?

 

[desnyder2001]

Array formula must be Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

You can use SumProduct or an Array Formula.

Did you provide a complete definition of overtime?
Does OT applies after say 8 hours?

Ok, I have everything working, NOW how to I have the OT cell not display unless it is a positive number? Only if there is actual overtime? And yes OT is anything over 40 a week.

 

[Dave Patton]

Which formula are you using?

"NOW how to I have the OT cell not display unless it is a positive number?"

a) uncheck option
see Excel Options Advanced Display Options this Workbook
uncheck show a zero cells that have zero balance

b) try custom format

 

[desnyder2001]

Which formula are you using?

"NOW how to I have the OT cell not display unless it is a positive number?"

a) uncheck option
see Excel Options Advanced Display Options this Workbook
uncheck show a zero cells that have zero balance

b) try custom format

Dave, this is great. Thank you so much. I have one last question: Is there a way to be able to just type in 0600 instead of 06:00

Drinks are on me. You just saved me HOURS every week for my supervisors.