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

S

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



[TABLE="width: 712"]
<tbody>[TR]
[TD="class: xl72, width: 186, bgcolor: transparent"]A
[/TD]
[TD="class: xl72, width: 75, bgcolor: transparent"]B
[/TD]
[TD="class: xl76, width: 69, bgcolor: transparent"]C
[/TD]
[TD="class: xl72, width: 85, bgcolor: transparent"]D
[/TD]
[TD="class: xl72, width: 85, bgcolor: transparent"]E
[/TD]
[TD="class: xl76, width: 57, bgcolor: transparent"]F
[/TD]
[TD="class: xl72, width: 72, bgcolor: transparent"]G
[/TD]
[TD="class: xl72, width: 57, bgcolor: transparent"]H
[/TD]
[TD="class: xl76, width: 74, bgcolor: transparent"]I
[/TD]
[TD="class: xl72, width: 57, bgcolor: transparent"]J
[/TD]
[TD="class: xl72, width: 57, bgcolor: transparent"]K
[/TD]
[TD="class: xl76, width: 72, bgcolor: transparent"]L
[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 186, bgcolor: silver"]Last name First name
[/TD]
[TD="class: xl69, width: 75, bgcolor: silver"]Personnel Number
[/TD]
[TD="class: xl87, width: 69, bgcolor: silver"]Date
[/TD]
[TD="class: xl88, width: 85, bgcolor: yellow"]Start time
[/TD]
[TD="class: xl89, width: 85, bgcolor: silver"]End time
[/TD]
[TD="class: xl69, width: 57, bgcolor: silver"]Number (unit)
[/TD]
[TD="class: xl69, width: 72, bgcolor: silver"]Att./Absence type
[/TD]
[TD="class: xl71, width: 57, bgcolor: silver"]Helper Column 1
[/TD]
[TD="class: xl71, width: 74, bgcolor: silver"]Helper Column 2
[/TD]
[TD="class: xl71, width: 57, bgcolor: silver"][/TD]
[TD="class: xl71, width: 57, bgcolor: silver"][/TD]
[TD="class: xl71, width: 72, bgcolor: silver"]Help[er column 3
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl77, width: 75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl77, width: 72, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]START TIME
[/TD]
[TD="class: xl80, width: 57, bgcolor: transparent"][/TD]
[TD="class: xl80, width: 57, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"] Finish
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl84, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]4:43 AM
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]2:54 PM
[/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: transparent"]SMITH FRANK
[/TD]
[TD="class: xl81, bgcolor: transparent"]123456
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/23/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]4:43:00 AM
[/TD]
[TD="class: xl83, bgcolor: transparent"]11:05:00 AM
[/TD]
[TD="class: xl86, bgcolor: transparent"]6.370
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl74, bgcolor: #FFFF99"]123456
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/23/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]11:32:00 AM
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]2:54:00 PM
[/TD]
[TD="class: xl68, bgcolor: #FFFF99"]3.370
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]SMITH FRANK
[/TD]
[TD="class: xl81, bgcolor: transparent"]123456
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/23/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl86, bgcolor: transparent"]9.740
[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]4:45 AM
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]3:15 PM
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]SMITH FRANK
[/TD]
[TD="class: xl81, bgcolor: transparent"]123456
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/25/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]4:45:00 AM
[/TD]
[TD="class: xl83, bgcolor: transparent"]10:33:00 AM
[/TD]
[TD="class: xl86, bgcolor: transparent"]5.800
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"]SMITH FRANK
[/TD]
[TD="class: xl74, bgcolor: #FFFF99"]123456
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/25/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]11:03:00 AM
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]3:15:00 PM
[/TD]
[TD="class: xl68, bgcolor: #FFFF99"]4.200
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"]123456
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/25/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl86, bgcolor: transparent"]10.000
[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]4:44 AM
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]2:32 PM
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"]SMITH FRANK
[/TD]
[TD="class: xl74, bgcolor: #FFFF99"]123456
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/26/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]4:44:00 AM
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]11:22:00 AM
[/TD]
[TD="class: xl68, bgcolor: #FFFF99"]6.630
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]SMITH FRANK
[/TD]
[TD="class: xl81, bgcolor: transparent"]123456
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/26/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]11:50:00 AM
[/TD]
[TD="class: xl83, bgcolor: transparent"]2:32:00 PM
[/TD]
[TD="class: xl86, bgcolor: transparent"]2.700
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl74, bgcolor: #FFFF99"]123456
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/26/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl68, bgcolor: #FFFF99"]9.330
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"]123456
[/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl86, bgcolor: transparent"]29.070
[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent"] 6:32:00 PM
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"] 4:47:00 AM
[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: #FFFF99"]ZONKER JOE
[/TD]
[TD="class: xl74, bgcolor: #FFFF99"]654321
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/23/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]6:32:00 PM
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]11:48:00 PM
[/TD]
[TD="class: xl68, bgcolor: #FFFF99"]5.270
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/23/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl86, bgcolor: transparent"]5.270
[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent"] 5:33:00 PM
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"] 5:24 AM
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"]ZONKER JOE
[/TD]
[TD="class: xl74, bgcolor: #FFFF99"]654321
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/24/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]12:21:00 AM
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]4:47:00 AM
[/TD]
[TD="class: xl68, bgcolor: #FFFF99"]4.430
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]ZONKER JOE
[/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/24/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]5:33:00 PM
[/TD]
[TD="class: xl83, bgcolor: transparent"]10:51:00 PM
[/TD]
[TD="class: xl86, bgcolor: transparent"]5.300
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]ZONKER JOE
[/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/24/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]11:24:00 PM
[/TD]
[TD="class: xl83, bgcolor: transparent"]5:24:00 AM
[/TD]
[TD="class: xl86, bgcolor: transparent"]6.000
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl74, bgcolor: #FFFF99"]654321
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/24/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl68, bgcolor: #FFFF99"]15.730
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]6:04 PM
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]4:49 AM
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]ZONKER JOE
[/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/25/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]6:04:00 PM
[/TD]
[TD="class: xl83, bgcolor: transparent"]11:27:00 PM
[/TD]
[TD="class: xl86, bgcolor: transparent"]5.380
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]ZONKER JOE
[/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/25/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]11:58:00 PM
[/TD]
[TD="class: xl83, bgcolor: transparent"]4:49:00 AM
[/TD]
[TD="class: xl86, bgcolor: transparent"]4.850
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl74, bgcolor: #FFFF99"]654321
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/25/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl68, bgcolor: #FFFF99"]10.230
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]5:48 PM
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"] 4:17 AM
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]ZONKER JOE
[/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/26/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]5:48:00 PM
[/TD]
[TD="class: xl83, bgcolor: transparent"]11:34:00 PM
[/TD]
[TD="class: xl86, bgcolor: transparent"]5.770
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/26/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl86, bgcolor: transparent"]5.770
[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]5:20 PM
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"] 5:14 AM
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"]ZONKER JOE
[/TD]
[TD="class: xl74, bgcolor: #FFFF99"]654321
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/27/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]12:05:00 AM
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"]4:17:00 AM
[/TD]
[TD="class: xl68, bgcolor: #FFFF99"]4.200
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]ZONKER JOE
[/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/27/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]5:20:00 PM
[/TD]
[TD="class: xl83, bgcolor: transparent"]10:06:00 PM
[/TD]
[TD="class: xl86, bgcolor: transparent"]4.770
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]ZONKER JOE
[/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/27/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]10:39:00 PM
[/TD]
[TD="class: xl83, bgcolor: transparent"]5:14:00 AM
[/TD]
[TD="class: xl86, bgcolor: transparent"]6.580
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl74, bgcolor: #FFFF99"]654321
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/27/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl68, bgcolor: #FFFF99"]15.550
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]5:36 PM
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]5:45 AM
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]ZONKER JOE
[/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/28/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]5:36:00 PM
[/TD]
[TD="class: xl83, bgcolor: transparent"]10:30:00 PM
[/TD]
[TD="class: xl86, bgcolor: transparent"]4.900
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]ZONKER JOE
[/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"]4/28/2018
[/TD]
[TD="class: xl83, bgcolor: transparent"]11:04:00 PM
[/TD]
[TD="class: xl83, bgcolor: transparent"]5:45:00 AM
[/TD]
[TD="class: xl86, bgcolor: transparent"]6.680
[/TD]
[TD="class: xl72, bgcolor: transparent"]REG
[/TD]
[TD="class: xl79, bgcolor: transparent"]v
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl74, bgcolor: #FFFF99"]654321
[/TD]
[TD="class: xl66, bgcolor: #FFFF99"]4/28/2018
[/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl67, bgcolor: #FFFF99"][/TD]
[TD="class: xl68, bgcolor: #FFFF99"]11.580
[/TD]
[TD="class: xl65, bgcolor: #FFFF99"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"]654321
[/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl86, bgcolor: transparent"]64.130
[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"]b
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#C0C0C0]Last name First name[/td][td=bgcolor:#C0C0C0]Personnel Number[/td][td=bgcolor:#C0C0C0]Date[/td][td=bgcolor:#FFFF00]Start time[/td][td=bgcolor:#C0C0C0]End time[/td][td=bgcolor:#C0C0C0]Number (unit)[/td][td=bgcolor:#C0C0C0]Att./Absence type[/td][td=bgcolor:#C0C0C0]Helper Column 2[/td][td=bgcolor:#C0C0C0]Help[er column 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
START TIME​
[/td][td=bgcolor:#FAFAFA]Finish[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
4:43 AM​
[/td][td=bgcolor:#FAFAFA]
2:54 PM​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#FAFAFA]SMITH FRANK[/td][td=bgcolor:#FAFAFA]
123456​
[/td][td=bgcolor:#FAFAFA]
4/23/2018​
[/td][td=bgcolor:#FAFAFA]
4:43:00 AM​
[/td][td=bgcolor:#FAFAFA]
11:05:00 AM​
[/td][td=bgcolor:#FAFAFA]
6.37​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
123456​
[/td][td=bgcolor:#FFFF99]
4/23/2018​
[/td][td=bgcolor:#FFFF99]
11:32:00 AM​
[/td][td=bgcolor:#FFFF99]
2:54:00 PM​
[/td][td=bgcolor:#FFFF99]
3.37​
[/td][td=bgcolor:#FFFF99]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#FAFAFA]SMITH FRANK[/td][td=bgcolor:#FAFAFA]
123456​
[/td][td=bgcolor:#FAFAFA]
4/23/2018​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
9.74​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
4:45 AM​
[/td][td=bgcolor:#FAFAFA]
3:15 PM​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#FAFAFA]SMITH FRANK[/td][td=bgcolor:#FAFAFA]
123456​
[/td][td=bgcolor:#FAFAFA]
4/25/2018​
[/td][td=bgcolor:#FAFAFA]
4:45:00 AM​
[/td][td=bgcolor:#FAFAFA]
10:33:00 AM​
[/td][td=bgcolor:#FAFAFA]
5.8​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#FFFF99]SMITH FRANK[/td][td=bgcolor:#FFFF99]
123456​
[/td][td=bgcolor:#FFFF99]
4/25/2018​
[/td][td=bgcolor:#FFFF99]
11:03:00 AM​
[/td][td=bgcolor:#FFFF99]
3:15:00 PM​
[/td][td=bgcolor:#FFFF99]
4.2​
[/td][td=bgcolor:#FFFF99]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
123456​
[/td][td=bgcolor:#FAFAFA]
4/25/2018​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
10​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
4:44 AM​
[/td][td=bgcolor:#FAFAFA]
2:32 PM​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td=bgcolor:#FFFF99]SMITH FRANK[/td][td=bgcolor:#FFFF99]
123456​
[/td][td=bgcolor:#FFFF99]
4/26/2018​
[/td][td=bgcolor:#FFFF99]
4:44:00 AM​
[/td][td=bgcolor:#FFFF99]
11:22:00 AM​
[/td][td=bgcolor:#FFFF99]
6.63​
[/td][td=bgcolor:#FFFF99]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td=bgcolor:#FAFAFA]SMITH FRANK[/td][td=bgcolor:#FAFAFA]
123456​
[/td][td=bgcolor:#FAFAFA]
4/26/2018​
[/td][td=bgcolor:#FAFAFA]
11:50:00 AM​
[/td][td=bgcolor:#FAFAFA]
2:32:00 PM​
[/td][td=bgcolor:#FAFAFA]
2.7​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
123456​
[/td][td=bgcolor:#FFFF99]
4/26/2018​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
9.33​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
123456​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
29.07​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
6:32 PM​
[/td][td=bgcolor:#FAFAFA]
4:47 AM​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td=bgcolor:#FFFF99]ZONKER JOE[/td][td=bgcolor:#FFFF99]
654321​
[/td][td=bgcolor:#FFFF99]
4/23/2018​
[/td][td=bgcolor:#FFFF99]
6:32:00 PM​
[/td][td=bgcolor:#FFFF99]
11:48:00 PM​
[/td][td=bgcolor:#FFFF99]
5.27​
[/td][td=bgcolor:#FFFF99]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/23/2018​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
5.27​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
5:33 PM​
[/td][td=bgcolor:#FAFAFA]
12:00 AM​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td=bgcolor:#FFFF99]ZONKER JOE[/td][td=bgcolor:#FFFF99]
654321​
[/td][td=bgcolor:#FFFF99]
4/24/2018​
[/td][td=bgcolor:#FFFF99]
12:21:00 AM​
[/td][td=bgcolor:#FFFF99]
4:47:00 AM​
[/td][td=bgcolor:#FFFF99]
4.43​
[/td][td=bgcolor:#FFFF99]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td=bgcolor:#FAFAFA]ZONKER JOE[/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/24/2018​
[/td][td=bgcolor:#FAFAFA]
5:33:00 PM​
[/td][td=bgcolor:#FAFAFA]
10:51:00 PM​
[/td][td=bgcolor:#FAFAFA]
5.3​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td=bgcolor:#FAFAFA]ZONKER JOE[/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/24/2018​
[/td][td=bgcolor:#FAFAFA]
11:24:00 PM​
[/td][td=bgcolor:#FAFAFA]
5:24:00 AM​
[/td][td=bgcolor:#FAFAFA]
6​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
654321​
[/td][td=bgcolor:#FFFF99]
4/24/2018​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
15.73​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FAFAFA]
6:04 PM​
[/td][td=bgcolor:#FAFAFA]
4:49 AM​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td=bgcolor:#FAFAFA]ZONKER JOE[/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/25/2018​
[/td][td=bgcolor:#FAFAFA]
6:04:00 PM​
[/td][td=bgcolor:#FAFAFA]
11:27:00 PM​
[/td][td=bgcolor:#FAFAFA]
5.38​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td=bgcolor:#FAFAFA]ZONKER JOE[/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/25/2018​
[/td][td=bgcolor:#FAFAFA]
11:58:00 PM​
[/td][td=bgcolor:#FAFAFA]
4:49:00 AM​
[/td][td=bgcolor:#FAFAFA]
4.85​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
654321​
[/td][td=bgcolor:#FFFF99]
4/25/2018​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
10.23​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FAFAFA]
5:48 PM​
[/td][td=bgcolor:#FAFAFA]
4:17 AM​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td=bgcolor:#FAFAFA]ZONKER JOE[/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/26/2018​
[/td][td=bgcolor:#FAFAFA]
5:48:00 PM​
[/td][td=bgcolor:#FAFAFA]
11:34:00 PM​
[/td][td=bgcolor:#FAFAFA]
5.77​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/26/2018​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
5.77​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
5:20 PM​
[/td][td=bgcolor:#FAFAFA]
12:00 AM​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td=bgcolor:#FFFF99]ZONKER JOE[/td][td=bgcolor:#FFFF99]
654321​
[/td][td=bgcolor:#FFFF99]
4/27/2018​
[/td][td=bgcolor:#FFFF99]
12:05:00 AM​
[/td][td=bgcolor:#FFFF99]
4:17:00 AM​
[/td][td=bgcolor:#FFFF99]
4.2​
[/td][td=bgcolor:#FFFF99]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26​
[/td][td=bgcolor:#FAFAFA]ZONKER JOE[/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/27/2018​
[/td][td=bgcolor:#FAFAFA]
5:20:00 PM​
[/td][td=bgcolor:#FAFAFA]
10:06:00 PM​
[/td][td=bgcolor:#FAFAFA]
4.77​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27​
[/td][td=bgcolor:#FAFAFA]ZONKER JOE[/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/27/2018​
[/td][td=bgcolor:#FAFAFA]
10:39:00 PM​
[/td][td=bgcolor:#FAFAFA]
5:14:00 AM​
[/td][td=bgcolor:#FAFAFA]
6.58​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
654321​
[/td][td=bgcolor:#FFFF99]
4/27/2018​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
15.55​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FAFAFA]
5:36 PM​
[/td][td=bgcolor:#FAFAFA]
5:45 AM​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29​
[/td][td=bgcolor:#FAFAFA]ZONKER JOE[/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/28/2018​
[/td][td=bgcolor:#FAFAFA]
5:36:00 PM​
[/td][td=bgcolor:#FAFAFA]
10:30:00 PM​
[/td][td=bgcolor:#FAFAFA]
4.9​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30​
[/td][td=bgcolor:#FAFAFA]ZONKER JOE[/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA]
4/28/2018​
[/td][td=bgcolor:#FAFAFA]
11:04:00 PM​
[/td][td=bgcolor:#FAFAFA]
5:45:00 AM​
[/td][td=bgcolor:#FAFAFA]
6.68​
[/td][td=bgcolor:#FAFAFA]REG[/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
654321​
[/td][td=bgcolor:#FFFF99]
4/28/2018​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FFFF99]
11.58​
[/td][td=bgcolor:#FFFF99][/td][td=bgcolor:#FAFAFA]
​
[/td][td=bgcolor:#FAFAFA]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
32​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
654321​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA]
64.13​
[/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet10[/td][/tr][/table]


Formula in H3 and copy down.[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
H​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#FAFAFA]
=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,"")))​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet10[/td][/tr][/table]


Formula in I3 and copy down[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#FAFAFA]
=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,"")))​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet10[/td][/tr][/table]
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Finding Numbers from a Column Using Target Numbers
2
Replies
11
Views
431
Cubist
Cubist
Z
  • Solved
Formula to find 'Part of the Day' from the Time Column
Replies
8
Views
599
Zee996
Z
J
  • Question Question
XLOOKUP, Finding a Value based on Time and Date
Replies
1
Views
378
SunnyAlv
SunnyAlv
N
  • Question Question
Advisor login, logout & break calculation
Replies
1
Views
254
Cubist
Cubist
A
  • Question Question
Hi everyone. Help wit Excel!
Replies
2
Views
285
StephenCrump
S

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top