Excel Help Finding the start and finish time in sheet for each person per day

[Seabee1]

Hello, Need help with a formula that will find the first two blank cells in D column and then return back a start time and end. I been wracking my brain for a few weeks but still doesn't turn out right. My time system for night employees will add their lunch break return to the next day if they clock out after midnight. I Tried using a helper column but didn't work well. see example below
Starts with 2 BLANK CELLS in column D then I need the first time in row 3 column D for the start in column I then I need the 2nd time in row 4 column E as a finish time then continue to evaluate until next double blank cell which is the unique break character for the employees. any help would be greatly appreciated. thank you. the Start time in column I and finish time in column L have been added to demonstrate how I would like to capture the information. again thanks

A	B	C	D	E	F	G	H	I	J	K	L
Last name First name	Personnel Number	Date	Start time	End time	Number (unit)	Att./Absence type	Helper Column 1	Helper Column 2			Help[er column 3
							b	START TIME			Finish
							b	4:43 AM			2:54 PM
SMITH FRANK	123456	4/23/2018	4:43:00 AM	11:05:00 AM	6.370	REG	v
	123456	4/23/2018	11:32:00 AM	2:54:00 PM	3.370	REG	v
SMITH FRANK	123456	4/23/2018			9.740		b	4:45 AM			3:15 PM
SMITH FRANK	123456	4/25/2018	4:45:00 AM	10:33:00 AM	5.800	REG	v
SMITH FRANK	123456	4/25/2018	11:03:00 AM	3:15:00 PM	4.200	REG	v
	123456	4/25/2018			10.000		b	4:44 AM			2:32 PM
SMITH FRANK	123456	4/26/2018	4:44:00 AM	11:22:00 AM	6.630	REG	v
SMITH FRANK	123456	4/26/2018	11:50:00 AM	2:32:00 PM	2.700	REG	v
	123456	4/26/2018			9.330		b
	123456				29.070		b	6:32:00 PM			4:47:00 AM
ZONKER JOE	654321	4/23/2018	6:32:00 PM	11:48:00 PM	5.270	REG	v
	654321	4/23/2018			5.270		b	5:33:00 PM			5:24 AM
ZONKER JOE	654321	4/24/2018	12:21:00 AM	4:47:00 AM	4.430	REG	v
ZONKER JOE	654321	4/24/2018	5:33:00 PM	10:51:00 PM	5.300	REG	v
ZONKER JOE	654321	4/24/2018	11:24:00 PM	5:24:00 AM	6.000	REG	v
	654321	4/24/2018			15.730		b	6:04 PM			4:49 AM
ZONKER JOE	654321	4/25/2018	6:04:00 PM	11:27:00 PM	5.380	REG	v
ZONKER JOE	654321	4/25/2018	11:58:00 PM	4:49:00 AM	4.850	REG	v
	654321	4/25/2018			10.230		b	5:48 PM			4:17 AM
ZONKER JOE	654321	4/26/2018	5:48:00 PM	11:34:00 PM	5.770	REG	v
	654321	4/26/2018			5.770		b	5:20 PM			5:14 AM
ZONKER JOE	654321	4/27/2018	12:05:00 AM	4:17:00 AM	4.200	REG	v
ZONKER JOE	654321	4/27/2018	5:20:00 PM	10:06:00 PM	4.770	REG	v
ZONKER JOE	654321	4/27/2018	10:39:00 PM	5:14:00 AM	6.580	REG	v
	654321	4/27/2018			15.550		b	5:36 PM			5:45 AM
ZONKER JOE	654321	4/28/2018	5:36:00 PM	10:30:00 PM	4.900	REG	v
ZONKER JOE	654321	4/28/2018	11:04:00 PM	5:45:00 AM	6.680	REG	v
	654321	4/28/2018			11.580		b
	654321				64.130		b

<tbody>
</tbody>

 

[iggy_]

You can have something like this depending if the rest of the data is consistent, but may want to reorganize your table aswell.

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Last name First name	Personnel Number	Date	Start time	End time	Number (unit)	Att./Absence type	Helper Column 2	Help[er column 3
2​								START TIME​	Finish
3​								4:43 AM​	2:54 PM​
4​	SMITH FRANK	123456​	4/23/2018​	4:43:00 AM​	11:05:00 AM​	6.37​	REG	​	​
5​		123456​	4/23/2018​	11:32:00 AM​	2:54:00 PM​	3.37​	REG	​	​
6​	SMITH FRANK	123456​	4/23/2018​			9.74​		4:45 AM​	3:15 PM​
7​	SMITH FRANK	123456​	4/25/2018​	4:45:00 AM​	10:33:00 AM​	5.8​	REG	​	​
8​	SMITH FRANK	123456​	4/25/2018​	11:03:00 AM​	3:15:00 PM​	4.2​	REG	​	​
9​		123456​	4/25/2018​			10​		4:44 AM​	2:32 PM​
10​	SMITH FRANK	123456​	4/26/2018​	4:44:00 AM​	11:22:00 AM​	6.63​	REG	​	​
11​	SMITH FRANK	123456​	4/26/2018​	11:50:00 AM​	2:32:00 PM​	2.7​	REG	​	​
12​		123456​	4/26/2018​			9.33​		​	​
13​		123456​				29.07​		6:32 PM​	4:47 AM​
14​	ZONKER JOE	654321​	4/23/2018​	6:32:00 PM​	11:48:00 PM​	5.27​	REG	​	​
15​		654321​	4/23/2018​			5.27​		5:33 PM​	12:00 AM​
16​	ZONKER JOE	654321​	4/24/2018​	12:21:00 AM​	4:47:00 AM​	4.43​	REG	​	​
17​	ZONKER JOE	654321​	4/24/2018​	5:33:00 PM​	10:51:00 PM​	5.3​	REG	​	​
18​	ZONKER JOE	654321​	4/24/2018​	11:24:00 PM​	5:24:00 AM​	6​	REG	​	​
19​		654321​	4/24/2018​			15.73​		6:04 PM​	4:49 AM​
20​	ZONKER JOE	654321​	4/25/2018​	6:04:00 PM​	11:27:00 PM​	5.38​	REG	​	​
21​	ZONKER JOE	654321​	4/25/2018​	11:58:00 PM​	4:49:00 AM​	4.85​	REG	​	​
22​		654321​	4/25/2018​			10.23​		5:48 PM​	4:17 AM​
23​	ZONKER JOE	654321​	4/26/2018​	5:48:00 PM​	11:34:00 PM​	5.77​	REG	​	​
24​		654321​	4/26/2018​			5.77​		5:20 PM​	12:00 AM​
25​	ZONKER JOE	654321​	4/27/2018​	12:05:00 AM​	4:17:00 AM​	4.2​	REG	​	​
26​	ZONKER JOE	654321​	4/27/2018​	5:20:00 PM​	10:06:00 PM​	4.77​	REG	​	​
27​	ZONKER JOE	654321​	4/27/2018​	10:39:00 PM​	5:14:00 AM​	6.58​	REG	​	​
28​		654321​	4/27/2018​			15.55​		5:36 PM​	5:45 AM​
29​	ZONKER JOE	654321​	4/28/2018​	5:36:00 PM​	10:30:00 PM​	4.9​	REG	​	​
30​	ZONKER JOE	654321​	4/28/2018​	11:04:00 PM​	5:45:00 AM​	6.68​	REG	​	​
31​		654321​	4/28/2018​			11.58​		​	​
32​		654321​				64.13​

Sheet: Sheet10

Formula in H3 and copy down.

	H​
3​	=IF(AND(ISBLANK(D3),NOT(ISBLANK(D4)),NOT(ISBLANK(D5)),NOT(ISBLANK(D6))),D5,IF(AND(ISBLANK(D3),NOT(ISBLANK(D4)),ISBLANK(D5)),D4,IF(AND(ISBLANK(D3),NOT(ISBLANK(D4))),D4,"")))​

Sheet: Sheet10

Formula in I3 and copy down

	I​
3​	=IF(AND(ISBLANK(E3),NOT(ISBLANK(E4)),NOT(ISBLANK(E5)),NOT(ISBLANK(E6))),E7,IF(AND(ISBLANK(E3),NOT(ISBLANK(E4)),ISBLANK(E5)),E6,IF(AND(ISBLANK(E3),NOT(ISBLANK(E4))),E5,"")))​

Sheet: Sheet10

 

[Seabee1]

Iggy thank you got me a lot closer than I was. I will need to add 1 more IF statement and I think it will capture all.