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]

Hi Trush12,

Can you give an example of the input times, along with any shift name/number they may contain.

Can you give an example of the output you'd like to see?

 

[trush12]

Thanks so much!

Shifts are normally 12 hour shifts with 36 hours weekly. Anything above 40 is considered OT. Weeks run Sunday to Saturday:

Dayshift: 7a-7:30p
Nightshift: 7:00p-7:30a
Mid-shift1: 10a-10:30p
Mid-shift2: 1p-1:30a

For example:

If I work, Sunday, Monday, Tuesday on Dayshift (7a-7:30p), I would get the following shift diffs:
Shift 1 (Day)= 24 hours
Shift 2 (Eve)= 12 hours
Shift 4 (WK Day)= 8 hours
Shift 5 (WK Eve)= 4 hours
Shift 7 (ED SPEC)= 12 hours

I would just like to be able to put in the Time In and Time Out and have each differential broken down into hours to send to the payroll dept.

 

[Toadstool]

I'm still not sure what calculation you want.
"I would just like to be able to put in the Time In and Time Out and have each differential broken down into hours to send to the payroll dept."
So if you enter 7th January 2022 (a Friday) clock-in at 18:30 with a 8th January (a Saturday) clock-out at 07:30 then that would be 4.5 hours of a Shift 2, 0.5 hours of a Shift 4, 8 hours of a Shift 6 and 10.5 hours of a Shift 7? So do you just want the total of 23.5 hours or presented as the four different shift times?

 

[SpillerBD]

When you record the Time, you should record the Date as well in the same cell.
NOW can be entered with CTRL+: , CTRL+;, Enter

@*trucsh12, and I also don't see what your calculation must be used to get the results in your 2nd post

 

[trush12]

Thank you for the help...Does this make sense?

		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
Nurse, Betty		SUN	MON	TUE	WED	THU	FRI	SAT		SUN	MON	TUE	WED	THU	FRI	SAT	TOTALS
Time In & Time Out (Hrs)		24HR OC	1000-2230				1000-2230	1000-2230		1000-2230				1900-0730	1900-0730		72.00
OT= Anything over 40 hours in one week																	-
Shift 1= Day (Regular)- Monday-Friday 7AM-2:59PM	NA		5.00				5.00	5.00		5.00							20.00
Shift 2= BRX ER Evening - Monday-Friday 3PM-10:59PM	$1.00​		7.00				7.00	7.00		7.00				4.00	4.00		36.00
Shift 3= BRX ER Night - Monday-Friday 11PM-6:59AM	$2.00​													8.00	8.00		16.00
Shift 4= Wnkd Day - Saturday-Sunday 7AM-2:59PM	$1.35​							5.00		5.00							10.00
Shift 5= Wknd Eve - Saturday-Sunday 3PM-10:59PM	$2.25​							7.00		7.00							14.00
Shift 6= Wknd Night - Saturday-Sunday 11PM-6:59AM	$2.55​
Shift 7= BRX (ED SPEC) - Friday 7PM-Monday 7:30AM	$3.00​						3.00	12.00		12.00					12.00		39.00
On Call		24.00															24.00
Call Back
PTO
Tier 3 CS
Holiday
*30 min lunch unpaid

 

[Toadstool]

I'm still struggling to understand much.

1. Why is there 5 hours on Saturday 4 December against Shift 1 as that's designated as "Monday-Friday 7AM-2:59PM"?
2. I don't see where Overtime should go but it says "per week" so I've shown it against Saturday. I therefore expect that if you work 44 hours one week and 36 hours the next then you would show 4 hours O/T and 0 hours O/T but you'd still be paid 4 hours O/T.
3. I don't understand how the 30 minutes unpaid lunch is handled. How can you tell when it should be subtracted?
4. I don't understand what should be done with the rows for Call Back, PTO, Tier 3 CS, Holiday.

Here's my 1st attempt:

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-2230				1000-2230	1000-2230		1000-2230				1900-0730	1900-0730
4								Start		29-Nov 10:00				3-Dec 10:00	4-Dec 10:00		5-Dec 10:00				9-Dec 20:30	10-Dec 20:30
5								End		29-Nov 22:30				3-Dec 22:30	4-Dec 22:30		5-Dec 22:30				10-Dec 07:30	11-Dec 07:30
6								Hours		12.5				12.5	12.5		12.5				11	11		72
7							OT= Anything over 40 hours in one week								28.5								4.5	33
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.5				7.5							2.5	2.5		20
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.5		7.5							15
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.5				3.5								11		18
15							On Call		24															24
16							Call Back
17							PTO
18							Tier 3 CS
19							Holiday
20							*30 min lunch unpaid
1st

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

 

[trush12]

I am sorry for the confusion. I am in over my head a bit This is very helpful!

1. My mistake on the Dec 4 and Dec 5 for Shift 1 and also on Shift 2 for those days
2. You are correct on the OT
3. Lunch is never paid...they work 12.5 hours but only get paid for 12.
4. ON CALL, Call Back, PTO, Tier 3 CS, Holiday is nothing I need calculated.

 

[Toadstool]

1. My mistake on the Dec 4 and Dec 5 for Shift 1 and also on Shift 2 for those days
OK

2. You are correct on the OT
A good guess!

3. Lunch is never paid...they work 12.5 hours but only get paid for 12.
This is where it gets confusing.
Your sample for Friday 3rd December had worked hours of 10:00 to 22:30 and you show:
Shift 1 has 5 hours.
Shift 2 has 7 hours.
Shift 7 has 3 hours.
In my example:
Shift 1 has 5 hours (because the half hour extra isn't in the shift range of 7am to 3pm)
Shift 2 has 7.5 hours.
Shift 7 has 3.5 hours.

I can't subtract 30 minutes from every calculation because the overlap might not include that extra half hour, as in my Shift 1 calculation.
What would you like me to try?

4. ON CALL, Call Back, PTO, Tier 3 CS, Holiday is nothing I need calculated.
Good news!

 

[trush12]

What if we change hours worked as follows: 10:00-22:00, 19:00-07:00...etc. and eliminate the 30 minutes altogether?