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

 

[Dave Patton]

You did not answer the question or indicate which format options you are using.

Yes it is possible to convert 0600 to time.

Why are you using time instead of regular numbers such as 8 or 8.25?

 

[desnyder2001]

You did not answer the question or indicate which format options you are using.

Yes it is possible to convert 0600 to time.

Why are you using time instead of regular numbers such as 8 or 8.25?

The format for the time that I am using is [h]:mm

As far as why I am using time instead of numbers..... well everything I am doing is based off of research on Google and this forum. I need a formula that would allow me to have someone start work at 22:00 and end 06:00. The cross over at midnight was giving me trouble and I found someone off the website that posted how they did it. Which was with the [h]:mm format.

The basic thing I need is of course a timesheet that has IN - OUT - Total for each day. Then a Weekly total of Reg Hours + OT hours (anything over 40 a week).
If there is a way to attach a file I could send you what I have and you could give advise? I'm not sure how these forums work. I'm new to all this.

 

[Dave Patton]

The business information must be clearly defined and reflected in the spreadsheet.
Select the version and/or options that you prefer.

You can expand this mini version to address your actual situation.

Please see forum posts that provide:
- forum information
Guidelines for Forum Use

Forum Rules


- suggested Add-ins including tools to facilitate posting a concise example of your challenge to the forum
(top of page 1 of main messages)

https://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1							Overtime in hours							Rate -->	$60
2			Mon 07-Aug-2017			Tue 08-Aug-2017			Wed 09-Aug-2017			Total	Regular	Overtime	Amount
3			Start	End	DayTot	Start	End	DayTot	Start	End	DayTot	DayTot
4
5	time as text	version 1	0700	1600	9	2200	715	9.25	1200	1145	23.75	42.00	40.00	2.00	$2,580
6
7	enties as time ":"	version 2	7:00	16:00	9	22:00	7:15	9.25	12:00	2:00	14	32.25	32.25		$1,935
8
9	enties as time ":"	version 3	7:00	16:00	9	22:00	7:15	9.25	12:00	11:45	23.75	42.00	40.00	2.00	$2,580
4cc

Cell Formulas
Range	Formula
G2	=D2+1
J2	=G2+1
F5	=MOD(DOLLARDE(E5/100, 60) - DOLLARDE(D5/100, 60), 24)
F7	=MOD(E7-D7,1)*24
F9	=IF(D9>E9,(1-D9+E9)*24,(E9-D9)*24)
I5	=MOD(DOLLARDE(H5/100, 60) - DOLLARDE(G5/100, 60), 24)
I7	=MOD(H7-G7,1)*24
I9	=IF(G9>H9,(1-G9+H9)*24,(H9-G9)*24)
L5	=MOD(DOLLARDE(K5/100, 60) - DOLLARDE(J5/100, 60), 24)
L7	=MOD(K7-J7,1)*24
L9	=IF(J9>K9,(1-J9+K9)*24,(K9-J9)*24)
M5	=SUMPRODUCT(--($D$3:$L$3=$M$3),(D5:L5))
M7	=SUMPRODUCT(--($D$3:$L$3=$M$3),(D7:L7))
M9	=SUMPRODUCT(--($D$3:$L$3=$M$3),(D9:L9))
N5	=MIN(M5,40)
N7	=MIN(M7,40)
N9	=MIN(M9,40)
O5	=M5-N5
O7	=M7-N7
O9	=M9-N9
P5	=(N5+O5*1.5)*$P$1
P7	=(N7+O7*1.5)*$P$1
P9	=(N9+O9*1.5)*$P$1