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

Seabee1

New Member
Joined
Feb 19, 2018
Messages
7
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>
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

iggy_

Board Regular
Joined
Mar 28, 2018
Messages
59
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 namePersonnel NumberDateStart timeEnd timeNumber (unit)Att./Absence typeHelper Column 2Help[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

New Member
Joined
Feb 19, 2018
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,335
Messages
5,528,096
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top