Get the values based on the date and time.

S

Sri_33

Board Regular
Joined
Sep 8, 2020
Messages
119
Office Version
  1. 2016
Platform
  1. Windows
left lookup.xlsx
ABCDEFGHIJKLMNO
1 Start End Start End Start End Start End Start End Start End Start End
2ID9/12/20219/12/20219/13/20219/13/20219/14/20219/14/20219/15/20219/15/20219/16/20219/16/20219/17/20219/17/20219/18/20219/18/2021
3sdfOFFOFFOFFOFF13:3013:4513:3013:4513:3013:4513:3013:4513:3013:45
4tfh13:4514:0013:4514:00OFFOFFOFFOFF13:4514:0013:4514:0013:4514:00
5ru13:4514:0013:4514:00OFFOFFOFFOFF13:4514:0013:4514:0013:4514:00
6xcv6:006:15OFFOFFOFFOFF6:006:156:006:156:006:156:006:15
7youOFFOFF5:456:005:456:005:456:005:456:005:456:00OFFOFF
8qwe5:456:005:456:005:456:005:456:005:456:00OFFOFFOFFOFF
9
10
11TimeDateID
1213:509/13/2021tfh
13ru
14
15
16
Sheet2
Cell Formulas
RangeFormula
D2:O2D2=B2+1


Hello all, with respect to the time and date provided ID's should be generated in the green highlighted cells, from the above mentioned table, we have start and stop time for a specific slots, it would be great if anyone help me with this.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Hi Sri_33,

Does this do what you want?

Sri_33.xlsx
ABCDEFGHIJKLMNO
1 Start End Start End Start End Start End Start End Start End Start End
2ID9/12/20219/12/20219/13/20219/13/20219/14/20219/14/20219/15/20219/15/20219/16/20219/16/20219/17/20219/17/20219/18/20219/18/2021
3sdfOFFOFFOFFOFF13:3013:4513:3013:4513:3013:4513:3013:4513:3013:45
4tfh13:4514:0013:4514:00OFFOFFOFFOFF13:4514:0013:4514:0013:4514:00
5ru13:4514:0013:4514:00OFFOFFOFFOFF13:4514:0013:4514:0013:4514:00
6xcv6:006:15OFFOFFOFFOFF6:006:156:006:156:006:156:006:15
7youOFFOFF5:456:005:456:005:456:005:456:005:456:00OFFOFF
8qwe5:456:005:456:005:456:005:456:005:456:00OFFOFFOFFOFF
9
10
11TimeDateID
1213:509/13/2021tfh
13ru
14 
15 
16 
Sheet1
Cell Formulas
RangeFormula
D2:O2D2=B2+1
D12:D16D12=IFERROR(INDEX($A$3:$A$8,AGGREGATE(15,6,ROW($A$3:$A$8)-ROW($A$2)/((OFFSET($A$2,1,MATCH($C$12,$B$2:$O$2,0),COUNTA($A$3:$A$8))<=$B$12)*(OFFSET($A$2,1,MATCH($C$12,$B$2:$O$2,0)+1,COUNTA($A$3:$A$8))>=$B$12)),ROW()-ROW($G$11))),"")
 
Upvote 0
Toadstool said:
Hi Sri_33,

Does this do what you want?

Sri_33.xlsx
ABCDEFGHIJKLMNO
1 Start End Start End Start End Start End Start End Start End Start End
2ID9/12/20219/12/20219/13/20219/13/20219/14/20219/14/20219/15/20219/15/20219/16/20219/16/20219/17/20219/17/20219/18/20219/18/2021
3sdfOFFOFFOFFOFF13:3013:4513:3013:4513:3013:4513:3013:4513:3013:45
4tfh13:4514:0013:4514:00OFFOFFOFFOFF13:4514:0013:4514:0013:4514:00
5ru13:4514:0013:4514:00OFFOFFOFFOFF13:4514:0013:4514:0013:4514:00
6xcv6:006:15OFFOFFOFFOFF6:006:156:006:156:006:156:006:15
7youOFFOFF5:456:005:456:005:456:005:456:005:456:00OFFOFF
8qwe5:456:005:456:005:456:005:456:005:456:00OFFOFFOFFOFF
9
10
11TimeDateID
1213:509/13/2021tfh
13ru
14 
15 
16 
Sheet1
Cell Formulas
RangeFormula
D2:O2D2=B2+1
D12:D16D12=IFERROR(INDEX($A$3:$A$8,AGGREGATE(15,6,ROW($A$3:$A$8)-ROW($A$2)/((OFFSET($A$2,1,MATCH($C$12,$B$2:$O$2,0),COUNTA($A$3:$A$8))<=$B$12)*(OFFSET($A$2,1,MATCH($C$12,$B$2:$O$2,0)+1,COUNTA($A$3:$A$8))>=$B$12)),ROW()-ROW($G$11))),"")
Click to expand...
Toadstool said:
Hi Sri_33,

Does this do what you want?

Sri_33.xlsx
ABCDEFGHIJKLMNO
1 Start End Start End Start End Start End Start End Start End Start End
2ID9/12/20219/12/20219/13/20219/13/20219/14/20219/14/20219/15/20219/15/20219/16/20219/16/20219/17/20219/17/20219/18/20219/18/2021
3sdfOFFOFFOFFOFF13:3013:4513:3013:4513:3013:4513:3013:4513:3013:45
4tfh13:4514:0013:4514:00OFFOFFOFFOFF13:4514:0013:4514:0013:4514:00
5ru13:4514:0013:4514:00OFFOFFOFFOFF13:4514:0013:4514:0013:4514:00
6xcv6:006:15OFFOFFOFFOFF6:006:156:006:156:006:156:006:15
7youOFFOFF5:456:005:456:005:456:005:456:005:456:00OFFOFF
8qwe5:456:005:456:005:456:005:456:005:456:00OFFOFFOFFOFF
9
10
11TimeDateID
1213:509/13/2021tfh
13ru
14 
15 
16 
Sheet1
Cell Formulas
RangeFormula
D2:O2D2=B2+1
D12:D16D12=IFERROR(INDEX($A$3:$A$8,AGGREGATE(15,6,ROW($A$3:$A$8)-ROW($A$2)/((OFFSET($A$2,1,MATCH($C$12,$B$2:$O$2,0),COUNTA($A$3:$A$8))<=$B$12)*(OFFSET($A$2,1,MATCH($C$12,$B$2:$O$2,0)+1,COUNTA($A$3:$A$8))>=$B$12)),ROW()-ROW($G$11))),"")
Click to expand...
you are a genius, thank you so much, it works :) (y)
 
Upvote 0
A non volatile option
+Fluff 1.xlsm
ABCDEFGHIJKLMNO
1 Start End Start End Start End Start End Start End Start End Start End
2ID12/09/202112/09/202113/09/202113/09/202114/09/202114/09/202115/09/202115/09/202116/09/202116/09/202117/09/202117/09/202118/09/202118/09/2021
3sdfOFFOFFOFFOFF0.56250.572916670.56250.572916670.56250.572916670.56250.572916670.56250.57291667
4tfh0.572916670.583333330.572916670.58333333OFFOFFOFFOFF0.572916670.583333330.572916670.583333330.572916670.58333333
5ru0.572916670.583333330.572916670.58333333OFFOFFOFFOFF0.572916670.583333330.572916670.583333330.572916670.58333333
6xcv0.250.26041667OFFOFFOFFOFF0.250.260416670.250.260416670.250.260416670.250.26041667
7youOFFOFF0.239583330.250.239583330.250.239583330.250.239583330.250.239583330.25OFFOFF
8qwe0.239583330.250.239583330.250.239583330.250.239583330.250.239583330.25OFFOFFOFFOFF
9
10
11TimeDateID
120.5763888913/09/2021tfh
13ru
14 
15 
16 
17
Data
Cell Formulas
RangeFormula
D2:O2D2=B2+1
D12:D16D12=IFERROR(INDEX($A$3:$A$8,AGGREGATE(15,6,(ROW($A$3:$A$8)-ROW($A$3)+1)/($B$2:$O$2=$C$12)/($B$3:$N$8<=$B$12)/($C$3:$O$8>=$B$12),ROWS(D$12:D12))),"")
 
Upvote 0
Fluff said:
A non volatile option
+Fluff 1.xlsm
ABCDEFGHIJKLMNO
1 Start End Start End Start End Start End Start End Start End Start End
2ID12/09/202112/09/202113/09/202113/09/202114/09/202114/09/202115/09/202115/09/202116/09/202116/09/202117/09/202117/09/202118/09/202118/09/2021
3sdfOFFOFFOFFOFF0.56250.572916670.56250.572916670.56250.572916670.56250.572916670.56250.57291667
4tfh0.572916670.583333330.572916670.58333333OFFOFFOFFOFF0.572916670.583333330.572916670.583333330.572916670.58333333
5ru0.572916670.583333330.572916670.58333333OFFOFFOFFOFF0.572916670.583333330.572916670.583333330.572916670.58333333
6xcv0.250.26041667OFFOFFOFFOFF0.250.260416670.250.260416670.250.260416670.250.26041667
7youOFFOFF0.239583330.250.239583330.250.239583330.250.239583330.250.239583330.25OFFOFF
8qwe0.239583330.250.239583330.250.239583330.250.239583330.250.239583330.25OFFOFFOFFOFF
9
10
11TimeDateID
120.5763888913/09/2021tfh
13ru
14 
15 
16 
17
Data
Cell Formulas
RangeFormula
D2:O2D2=B2+1
D12:D16D12=IFERROR(INDEX($A$3:$A$8,AGGREGATE(15,6,(ROW($A$3:$A$8)-ROW($A$3)+1)/($B$2:$O$2=$C$12)/($B$3:$N$8<=$B$12)/($C$3:$O$8>=$B$12),ROWS(D$12:D12))),"")
Click to expand...
Awesome, it works, thank you (y) :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Split items into Rows
Replies
2
Views
337
GreyFox8991
G
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
141
Peter_SSs
Peter_SSs
J
  • Solved
How to aggregate data based on multiple criteria?
Replies
8
Views
540
Jurez
J
G
  • Solved
Combine Dates with one UniqueID
Replies
1
Views
399
kevin9999
K
G
  • Question
Using Excel Macro to Rename Files
Replies
2
Views
677
Gooby
G

Forum statistics

Threads
1,215,064
Messages
6,122,941
Members
449,094
Latest member
teemeren

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