Time Sheet question

[tfowlerusa]

Good morning. I am trying to put a time sheet together and I am having issues with a few formulas. I have a Code Shift column that I am trying to use to sort time worked into a specific code. Code1 hours are 6am to 6pm, Code 2 6pm to 12am, and Code 3 from 12am to 6am. Then placing the time worked into the specific Code Shift Column. I have so problems with adjusting the times in the formula to put the times into the correct column. Also would love to have cells blank when there is not data to calculate. Any help would be appreciated. Thank you Todd

On Call Log.xlsx
	A	B	C	D	E	F	G	H
2			Hours		Code	Code 1 Shift	Code 2 Shift	Code 3 Shift
3	Employee	Date	Start Time	Stop Time	Shift	Total Time	Total Time	Time
4	Varon	20-Jan	12:00 PM	12:18 PM	Code 1	0:18
5			8:30 AM	9:30 AM	Code 1	1:00
6			12:20 PM	1:20 PM	Code 1	1:00
7			1:45 PM	2:00 PM	Code 1	0:15
8			3:00 PM	3:45 PM	Code 1	0:45
9			3:45 PM	4:00 PM	Code 1	0:15
10			4:00 PM	4:15 PM	Code 1	0:15
11			5:00 PM	6:00 PM	Code 3
12			6:45 PM	7:15 PM	Code 3
13			11:30 PM	12:00 AM	Code 1	#VALUE!
14					Code 3			0:00
15					Code 3			0:00
16					Code 3			0:00
17					Code 3			0:00
18					Code 3			0:00
19					Code 3			0:00
20					Code 3			0:00
21					Code 3			0:00
22					Code 3			0:00
23					Code 3			0:00
24					Code 3			0:00
25					Code 3			0:00
26					Code 3			0:00
27					Total Hours
Sheet1

Cell Formulas
Range	Formula
E4:E26	E4	=IF(AND(C4>=TIME(6,0,0), D4<TIME(18,0,0)), "Code 1", IF(AND(C4>=TIME(18,0,0), D4<TIME(24,0,0)), "Code 2", IF(OR(C4<TIME(6,0,0), D4>=TIME(24,0,0)), "Code 3", "Invalid Time")))
F4:F26	F4	=IF(E4="Code 1", TEXT(D4-C4, "h:mm"), "")
G4:G26	G4	=IF(E4="Code 2", TEXT(D4-C4, "h:mm"), "")
H4:H5,H7:H10,H13:H26	H4	=IF(E4="Code 3", TEXT(D4-C4, "h:mm"), "")
H6	H6	=IF(E6="Code 3", IF(ISNUMBER(C6), IF(ISNUMBER(D6), IF(C6 < D6, TEXT(D6 - C6, "h:mm"), TEXT(1 - C6 + D6, "h:mm")), ""), ""), "")

 

[tfowlerusa]

I used the first reply with formula and the Code 2 column is not working still. I am using Excel 365 so I am not sure if that is the issue. I am also formatting everything to custom hh:mm. Thank again for your help!

On Call Log.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
1								6:00:00	18:00:00	18:00:01	0:00:00	0:00:01	6:00:00
2		Hours		Code 1 Shift	Code 2 Shift	Code 3 Shift		Code 1		Code 2		Code 3
3	Date	Start Time	Stop Time	Total Time	Total Time	Time
4	20-Jan	12:00	12:18	0:18
5		8:30	9:30	1:00
6		12:20	13:20	1:00
7		13:45	14:00	0:15
8		15:00	15:45	0:45
9		15:45	16:00	0:15
10		16:00	16:15	0:15
11		17:00	18:00	1:00
12		18:45	19:15		#############
13		23:30	0:00		#############
14	21-Jan	1:00	2:00			1:00
15		5:45	6:00			0:15
16		6:15	9:00	2:45
17
18
19
20
21
22
23
24
25
26
27			Total Hours Worked	7:33	#############	1:15
28
Sheet1

Cell Formulas
Range	Formula
E4:E26	E4	=IF(AND(C4>=$I$1, C4<$J$1), MIN(D4, $J$1)-C4, "")
F4:F26	F4	=IF(AND(C4>$J$1, D4<=1), MIN(D4, $L$1)-C4, "")
G4:G26	G4	=IF(AND(C4>=$M$1, C4<=$N$1), MIN(D4, $N$1)-C4, "")
E27:G27	E27	=SUM(E4:E26)
J14	J14	=IF(AND(124>=$K$1, C12<$L$1), MIN(D12, $L$1)-MAX(C12, $K$1), "")

 

[Dave Patton]

Try the suggestions in post #10

 

[Dave Patton]

Put 1 in cell D13 not 0.

Time 2023.xlsm
	C	D	E	F	G
1
2	Hours		Code 1 Shift	Code 2 Shift	Code 3 Shift
3	Start Time	Stop Time	Total Time	Total Time	Time
4	12:00	12:18	0:18
5	8:30	9:30	1:00
6	12:20	13:20	1:00
7	13:45	14:00	0:15
8	15:00	15:45	0:45
9	15:45	16:00	0:15
10	16:00	16:15	0:15
11	17:00	18:00	1:00
12	18:45	19:15		0:30
13	23:30	0:00		0:30
14	1:00	2:00			1:00
15	5:45	6:00			0:16
16	6:15	9:00	2:45
17			7:33	1:00	1:16
2f

Cell Formulas
Range	Formula
E4:E16	E4	=MAX(0,MIN(D4,0.75)-MAX(C4,0.25,0))
F4:F16	F4	=MAX(0,MIN(D4,1)-MAX(C4,0.75,0))
G4:G16	G4	=CEILING(MAX(0,MIN(0.25,D4)-MAX(1/(24*60*60),C4,0)),1/(24*60))
E17:G17	E17	=SUM(E4:E16)

 

[tfowlerusa]

Dave,

Thank you, I did use information from Post #10. I have 2 more questions. How would I handle cross shifts Line 17. Also Why am I getting values in cells that should not have any? I tried to put "" instead of a zero at end of formula and it give me #VALUE in cell. I also changed formatting to Custom h:mm AM/PM and it seems to work ok. Again I can not thank you enough.

On Call Log.xlsx
	C	D	E	F	G
1			Code 1 Shift	Code 2 Shift	Code 3 Shift
2	Hours		6 am to 6 pm	6 pm to Midnight	6 pm to Midnight
3	Start Time	Stop Time	Time	Time	Time
4	12:00 PM	12:18 PM	0:18	0:00	0:00
5	8:30 AM	9:30 AM	1:00	0:00	0:00
6	12:20 PM	1:20 PM	1:00	0:00	0:00
7	1:45 PM	2:00 PM	0:15	0:00	0:00
8	3:00 PM	3:45 PM	0:45	0:00	0:00
9	3:45 PM	4:00 PM	0:15	0:00	0:00
10	4:00 PM	4:15 PM	0:15	0:00	0:00
11	5:00 PM	6:00 PM	1:00	0:00	0:00
12	6:45 PM	7:15 PM	0:00	0:30	0:00
13	11:30 PM	12:00 AM	0:00	0:30	0:00
14	12:15 AM	1:15 AM	0:00	0:00	1:00
15	12:15 AM	3:00 AM	0:00	0:00	2:45
16	9:00 PM	9:15 PM	0:00	0:15	0:00
17	10:00 PM	1:00 AM	0:00	0:00	0:00
18			12:00	6:00	6:00
19			12:00	6:00	6:00
20			12:00	6:00	6:00
21			12:00	6:00	6:00
22			12:00	6:00	6:00
23			12:00	6:00	6:00
24			12:00	6:00	6:00
25			12:00	6:00	6:00
26			12:00	6:00	6:00
27		Total Hours Worked	16:48	7:15	9:45
Sheet1

Cell Formulas
Range	Formula
E4:E26	E4	=MAX(0,MIN(D4,0.75)-MAX(C4,0.25,0))
F4:F26	F4	=MAX(0,MIN(D4,1)-MAX(C4,0.75,0))
G4:G26	G4	=CEILING(MAX(0,MIN(0.25,D4)-MAX(1/(24*60*60),C4,0)),1/(24*60))
E27:G27	E27	=SUM(E4:E26)

 

[Dave Patton]

Try using Excel's tool Formulas Formula Evaluate to review how the formula works and to test edits to the formula.

Time 2023.xlsm
	C	D	E	F	G
1			Code 1 Shift	Code 2 Shift	Code 3 Shift
2			6 am to 6 pm	6 pm to Midnight	Midnight to 6 AM
3	Start Time	Stop Time	Time	Time	Time
4	12:00	12:18	0:18
5	8:30	9:30	1:00
6	12:20	13:20	1:00
7	13:45	14:00	0:15
8	15:00	15:45	0:45
9	15:45	16:00	0:15
10	16:00	16:15	0:15
11	17:00	18:00	1:00
12	18:45	19:15		0:30
13	23:30	0:00		0:30
14	0:15	1:15			1:00
15	0:15	3:00			2:45
16	21:00	21:15		0:15
17	22:00	1:00		2:00	1:00
18
19
20
21
22
23
24
25
26
27		Total Hours Worked	4:48	3:15	4:45
28
2ff

Cell Formulas
Range	Formula
E4:E26	E4	=(C4>0)*MAX(0,(MIN(D4,0.75)-MAX(C4,0.25)))
F4:F26	F4	=LET(s,(C4>D4),(C4>0.75)*(IF(s,s-MAX(C4,0.75),D4-C4)))
G4:G26	G4	=LET(t,(C4>D4),IF(t,(D4-C4+t)-F4,AND(C4>0,C4<0.25)*(MIN(0.25,D4)-C4)))
E27:G27	E27	=SUM(E4:E26)

 

[tfowlerusa]

Dave, thank you for all your help. I was able to get it where I wanted it.