Time Sheet with Shift Differentials

[trush12]

Hello... I am new to this forum and am in desperate need of a spreadsheet to breakdown and calculate hours worked with the following shift differentials when Time in and Time Out is entered:

Shift 1 (Day) = Monday-Friday 7AM-2:59PM
Shift 2 (Eve) = Monday-Friday 3PM-10:59PM
Shift 3 (Night) = Monday-Friday 11PM-6:59AM
Shift 4 (WK Day) = Saturday-Sunday 7AM-2:59PM
Shift 5 (WK Eve) = Saturday-Sunday 3PM-10:59PM
Shift 6 (WK Night) = Friday-Saturday 11PM-6:59AM
Shift 7 (ED SPEC) = Friday 7PM-Monday 7:30AM

Can you help me please?

 

[Toadstool]

I hadn't tried to handle it so that works for me.
I found a mistake in one calculation so here's a new version which also has the half hours removed.

Trush12.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1									28-Nov	29-Nov	30-Nov	1-Dec	2-Dec	3-Dec	4-Dec		5-Dec	6-Dec	7-Dec	8-Dec	9-Dec	10-Dec	11-Dec
2							Nurse, Betty		SUN	MON	TUE	WED	THU	FRI	SAT		SUN	MON	TUE	WED	THU	FRI	SAT	TOTALS
3							Time In & Time Out (Hrs)		24HR OC	1000-2200				1000-2200	1000-2200		1000-2200				1900-0700	1900-0700
4								Start		29-Nov 10:00				3-Dec 10:00	4-Dec 10:00		5-Dec 10:00				9-Dec 19:00	10-Dec 19:00
5								End		29-Nov 22:00				3-Dec 22:00	4-Dec 22:00		5-Dec 22:00				10-Dec 07:00	11-Dec 07:00
6								Hours		12				12	12		12				12	12		72
7							OT= Anything over 40 hours in one week								26								8	34
8	Shift 1	Day	MONTUEWEDTHUFRI	7:00	15:00	0	Shift 1= Day (Regular)- Monday-Friday 7AM-2:59PM	NA		5				5							0	0		10
9	Shift 2	Eve	MONTUEWEDTHUFRI	15:00	23:00	0	Shift 2= BRX ER Evening - Monday-Friday 3PM-10:59PM	$1.00		7				7							4	4		22
10	Shift 3	Night	MONTUEWEDTHUFRI	23:00	7:00	1	Shift 3= BRX ER Night - Monday-Friday 11PM-6:59AM	$2.00		0				0							8	8		16
11	Shift 4	WK Day	SATSUN	7:00	15:00	0	Shift 4= Wnkd Day - Saturday-Sunday 7AM-2:59PM	$1.35							5		5							10
12	Shift 5	WK Eve	SATSUN	15:00	23:00	0	Shift 5= Wknd Eve - Saturday-Sunday 3PM-10:59PM	$2.25							7		7							14
13	Shift 6	WK Night	SATSUN	23:00	7:00	1	Shift 6= Wknd Night - Saturday-Sunday 11PM-6:59AM	$2.55							0		0							0
14	Shift 7	ED SPEC	FRIMON	19:00	7:30	1	Shift 7= BRX (ED SPEC) - Friday 7PM-Monday 7:30AM	$3.00		3				3								12		18
15							On Call		24															24
3rd

Cell Formulas
Range	Formula
Q2:W2,I2:O2	I2	=UPPER(TEXT(I$1,"ddd"))
I4:W4	I4	=IF(OR(I3="",I3="24HR OC"),"",I1+TIME(LEFT(I$3,2),MID(I$3,3,2),0))
I5:P5	I5	=IF(OR(I3="",I3="24HR OC"),"",IF(RIGHT(I$3,4)<LEFT(I$3,4),I1+1,I1)+TIME(MID(I$3,6,2),RIGHT(I$3,2),0))
Q5:W5	Q5	=IF(Q3="","",IF(RIGHT(Q$3,4)<LEFT(Q$3,4),Q1+1,Q1)+TIME(MID(Q$3,6,2),RIGHT(Q$3,2),0))
I6:W6	I6	=IF(I$5<>"",(I5-I4)*24,"")
X6:X15	X6	=SUM(I6:W6)
I7:W7	I7	=IF(I$2="SAT",MAX(0,SUM(C8:I15)-40),"")
I8:W15	I8	=IF(AND(I$3="24HR OC",$G8="On Call"),24,IF(AND(I$4<>"",ISNUMBER(SEARCH(I$2,$C8))),MAX(MIN(I$5,I$1+$E8+$F8)-MAX(I$4,I$1+$D8),0)*24,""))
F8:F14	F8	=IF(E8<D8,1,0)

 

[trush12]

That is awesome!!!!!!! THANK YOU! I have a question about the OT row...it is showing 34 hours of OT and should be 0.

 

[Toadstool]

If you add all the hours for the first week, 5, 5, 7, 7, 5, 7, 3, 3 and 24, they equal 66 which is 26 hours overtime.
If you add all the hours for the second week 4, 4, 8, 8, 5, 7 and 12 you get 48, which is 8 hours overtime.
26 + 8 = 34.

 

[trush12]

I am not adding all those hours as hours worked.
if 72 hours are worked total as stated in Row 6, then this a breakdown of how those hours are paid. Some of the differentials overlap.

For example: working a Saturday 1000-2200 (12 hours), you get a shift diff added for Day, Eve, and Wk Day, Wk Eve and ED SPEC but it still only a total of 12 hours.

 

[Toadstool]

So the formula in I7 should be

=IF(I$2="SAT",MAX(0,SUM(C6:I6)-40),"")

which is then copy & pasted across all cells to W7.

 

[trush12]

Wonderful! I can't thank you enough for your help!!!!

 

[Toadstool]

Wonderful! I can't thank you enough for your help!!!!

You're welcome.
Thanks for the feedback.