Timesheet Calculations

[Meucci98]

Good day, I am looking for help on some formulas for 3 different columns that would automatically calculate Straight time worked (ST), Overtime worked (OT) and Double time worked (DT) based on input in column A

Below is the example where A (hrs worked per day) is manual input and the other three columns would have the automatic calculations based on column A

Any help would be greatly appreciated.

HRS WORKED PER DAY	ST	OT	DT
8	8
9	8	1
10	8	2
11	8	2	1
12	8	2	2
13	8	2	3
14	8	2	4
15	8	2	5
16	8	2	6

 

[JamesCanale]

MrExcelPlayground16.xlsx
	B	C	D	E
5	Hrs	ST	OT	DT
6	5	5	0	0
7	6	6	0	0
8	7	7	0	0
9	8	8	0	0
10	9	8	1	0
11	10	8	2	0
12	11	8	2	1
13	12	8	2	2
14	13	8	2	3
15	14	8	2	4
16	15	8	2	5
17	16	8	2	6
18	17	8	2	7
19	18	8	2	8
20	19	8	2	9
21	20	8	2	10
22	21	8	2	11
23	22	8	2	12
24	23	8	2	13
25	24	8	2	14
Sheet23

Cell Formulas
Range	Formula
C6:C25	C6	=MIN(B6,8)
D6:D25	D6	=MIN(2,MAX(B6-8,0))
E6:E25	E6	=MAX(0,B6-10)

 

[Meucci98]

Thanks for the quick reply, the formulas is D6 and E6 work great, however the formula in C6 always returns 8 even when no data is in B6.

Is there a way to have C6 show zero untill data is input into B6?

 

[JamesCanale]

Good catch

MrExcelPlayground16.xlsx
	B	C	D	E
5	Hrs	ST	OT	DT
6		0	0	0
7		0	0	0
8	7	7	0	0
9	8	8	0	0
10	9	8	1	0
11	10	8	2	0
12	11	8	2	1
13	12	8	2	2
14	13	8	2	3
15	14	8	2	4
16	15	8	2	5
17	16	8	2	6
18	17	8	2	7
19	18	8	2	8
20	19	8	2	9
21	20	8	2	10
22	21	8	2	11
23	22	8	2	12
24	23	8	2	13
25	24	8	2	14
Sheet23

Cell Formulas
Range	Formula
C6:C25	C6	=IF(B6<>"",MIN(B6,8),0)
D6:D25	D6	=MIN(2,MAX(B6-8,0))
E6:E25	E6	=MAX(0,B6-10)

 

[Meucci98]

Much appreciated sir, works awesome.
I am also wondering if it is possible to have it where:

If cell A6 was a drop down with options for Dayshift (DS) and Nightshift(NS)

If NS then the hours would populate like so:

	DAYSHIFT
DS/NS	HRS	ST	OT	DT
DS	12	8	2	2
NS	12	4	2	6

Or is that too complicated

 

[JamesCanale]

MrExcelPlayground16.xlsx
	A	B	C	D	E
5	DS/NS	Hrs	ST	OT	DT
6	DS		0	0	0
7	NS		0	0	0
8	DS	6	6	0	0
9	NS	6	4	2	0
10	DS	7	7	0	0
11	NS	7	4	2	1
12	DS	8	8	0	0
13	NS	8	4	2	2
14	DS	9	8	1	0
15	NS	9	4	2	3
16	DS	10	8	2	0
17	NS	10	4	2	4
18	DS	11	8	2	1
19	NS	11	4	2	5
20	DS	12	8	2	2
21	NS	12	4	2	6
22	DS	13	8	2	3
23	NS	13	4	2	7
24	DS	14	8	2	4
25	NS	14	4	2	8
Sheet23

Cell Formulas
Range	Formula
C6:C25	C6	=IF(B6<>"",IF(A6="DS",MIN(B6,8),MIN(B6,4)),0)
D6:D25	D6	=IF(A4="DS",MIN(2,MAX(B6-8,0)),MIN(2,MAX(B6-4,0)))
E6:E25	E6	=IF(A6="DS",MAX(0,B6-10),MAX(0,B6-6))