Calculating double time after 12 hours plus sundays

[GingerGypsy]

Forgive me if this creates a redundant thread on this topic but I am at my wits end. I have searched all over the internet and can't find a solution to my specific excel problem. On time cards where double time is after 12 hours per mon-sat and all day sunday, how can I create a function for this?

 

[LittleJon]

Could you show an image of your worksheet?

 

[Dave Patton]

Excel 2010
	A	B	C
13		Hours	OT > 12
14	30-Oct-18	14.5	2.5
15	4-Nov-18	10.0	10.0
1b

Cell Formulas
Range	Formula
C14	=IF(WEEKDAY(A14,2)=7,B14,IF(B14>12,B14-12,0))
C15	=IF(WEEKDAY(A15,2)=7,B15,IF(B15>12,B15-12,0))

 

[GingerGypsy]

Could you show an image of your worksheet?

	PAY PERIOD:	9/23/18	TO:	9/29/18
SU	M	T	W	TH	F	SA	Total
9/23	9/24	9/25	9/26	9/27	9/28	9/29	Hours
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
							0.0
0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
			Regular hours			0.0
			Overtime hours (if any)		0.0
										sorry i'm not very forum savvy

<colgroup><col><col span="5"><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[GingerGypsy]

So what i'm trying to do is come up with one IF formula for the whole total for double time across the pay week. Sun Mon Tues Wed Thur Fri Sat, with all of sunday being double time and all the others being double time after 12 hours.

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G	H	I	J	K
19	Sun Sep 23, 18	Mon Sep 24, 18	Tue Sep 25, 18	Wed Sep 26, 18	Thu Sep 27, 18	Fri Sep 28, 18	Sat Sep 29, 18	Hours
20	6	8	8	14	14	14	8	72	12	60
1b

Cell Formulas
Range	Formula
I20	=SUM(B20:H20)
J20	=SUMPRODUCT(--(C20:G20>12),C20:G20-12)+B20
K20	=I20-J20

 

[Dave Patton]

correction

Excel 2010
	B	C	D	E	F	G	H	I	J	K
19	Sun Sep 23, 18	Mon Sep 24, 18	Tue Sep 25, 18	Wed Sep 26, 18	Thu Sep 27, 18	Fri Sep 28, 18	Sat Sep 29, 18	Hours
20	6	8	8	14	14	14	8	72	12	60
1b

Cell Formulas
Range	Formula
I20	=SUM(B20:H20)
J20	=SUMPRODUCT(--(C20:H20>12),C20:H20-12)+B20
K20	=I20-J20

 

[GingerGypsy]

correction
Excel 2010

	B	C	D	E	F	G	H	I	J	K
19	Sun Sep 23, 18	Mon Sep 24, 18	Tue Sep 25, 18	Wed Sep 26, 18	Thu Sep 27, 18	Fri Sep 28, 18	Sat Sep 29, 18	Hours
20	6	8	8	14	14	14	8	72	12	60

<tbody>
</tbody>

1b​

Worksheet Formulas Cell Formula I20 =SUM(B20:H20) J20 =SUMPRODUCT(--(C20:H20>12),C20:H20-12)+B20 K20 =I20-J20 <tbody> </tbody>

<tbody>
</tbody>

Thank you soooo much, that does work for what I need. But I can't figure out how to get the totals correct. Say there is a total of 61 hours worked for the week. 40 regular, 14 OT, 7 DT. I'm getting: 40 reg, 21 OT, 7 DT. What formula do I need to change to make the overtime not also have the double time in the total? Thanks so much, I am such a noob.

 

[Dave Patton]

You did not define OT @ 1.5
Your example is not clear.

Consider the 2 alternatives that follow:

Suggestion deleted.

What is a work week?
What hours are at 1.5?
What hours are at 2.0?
Which day is Sunday?
What rates apply to Saturday?
What are rates after 40 hours?

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
17
18									Total
19		23-Sep-18	24-Sep-18	25-Sep-18	26-Sep-18	27-Sep-18	28-Sep-18	29-Sep-18	Hours	OT	Total
20	Hours	5	7	13	13	8	10	5	61		61
21	Regular	0	7	8	8	8	8	5	44	-4	40
22	OT @ 2.0	5	0	1	1	0	0	0	7		7
23	OT @ 1.5	0	0	4	4	0	2	0	10	4	14
24
25	Hours	5	7	13	13	8	10	5	61
26	OT @ 2.0								7
27	Regular								40
28	OT @ 1.5								14
29
1b

Cell Formulas
Range	Formula
J21	=MIN(I21,40)-I21
B21	=MIN(8,B20)*(WEEKDAY(B19,2)<7)
B22	=(WEEKDAY(B19,2)=7)*B20+(AND(WEEKDAY(B19,2)<7,B20>12)*(B20-12))
B23	=B20-SUM(B21:B22)
I20	=SUM(B20:H20)
I21	=SUM(B21:H21)
I22	=SUM(B22:H22)
I23	=SUM(B23:H23)
I25	=SUM(B25:H25)
I26	=SUMPRODUCT(--(C25:H25>12),C25:H25-12)+B25
I27	=MIN(40,SUM(C25:H25)-SUMPRODUCT(--(C25:H25>8),C25:H25-8))
I28	=I25-I26-I27
K20	=I20+J20
K21	=I21+J21
K22	=I22+J22
K23	=I23+J23