find range of date and time in column B then copy adjacent cell in column A into column D

[nmk34]

i have two columns A and B, where column B has date(date and time), column A has data values.
i want to look in column B for example for all cells from 6am to 7am then copy all adjacent cells in column A to column D. Thanks

 

[kevin9999]

You haven't said whether you're looking for a formula or VBA solution. If you're open to a formula, the following should give you what you want.

date time.xlsm
	A	B	C	D	E	F	G
1	data	Date/time		target		Parametetrs
2	Row 2 data	23/05/2022 5:03				6:00 AM
3	Row 3 data	23/05/2022 6:03		Row 3 data		7:00 AM
4	Row 4 data	23/05/2022 7:03
5	Row 5 data	23/05/2022 8:03
6	Row 6 data	23/05/2022 5:03
7	Row 7 data	23/05/2022 6:03		Row 7 data
8	Row 8 data	23/05/2022 7:03
9	Row 9 data	23/05/2022 8:03
10	Row 10 data	23/05/2022 5:03
11	Row 11 data	23/05/2022 6:03		Row 11 data
12	Row 12 data	23/05/2022 7:03
13	Row 13 data	23/05/2022 8:03
14	Row 14 data	23/05/2022 5:03
15	Row 15 data	23/05/2022 6:03		Row 15 data
16	Row 16 data	23/05/2022 7:03
17	Row 17 data	23/05/2022 8:03
18	Row 18 data	23/05/2022 7:03
19	Row 19 data	23/05/2022 8:03
20	Row 20 data	23/05/2022 6:03		Row 20 data
21
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=IF(AND(TIME(HOUR(B2),MINUTE(B2),)>=$F$2,TIME(HOUR(B2),MINUTE(B2),)<=$F$3),A2,"")

 

[kevin9999]

If you wanted VBA to do this for you, assuming the same sheet layout as above, then:

Option Explicit
Sub Between_Two_Times()
    Dim ws As Worksheet, LRow As Long
    Set ws = ActiveSheet
    LRow = ws.Cells(Rows.Count, 1).End(3).Row
    
    With ws.Range("D2:D" & LRow)
        .Formula = "=IF(AND(TIME(HOUR(B2),MINUTE(B2),)>=$F$2,TIME(HOUR(B2),MINUTE(B2),)<=$F$3),A2,"""")"
        .Value = .Value
    End With

End Sub

 

[Peter_SSs]

Another possible interpretation of what is wanted.

22 05 22.xlsm
	A	B	C	D	E	F
1	data	Date/time		target		Parametetrs
2	Row 2 data	23/05/2022 5:03		Row 3 data		6:00:00 AM
3	Row 3 data	23/05/2022 6:03		Row 7 data		7:00:00 AM
4	Row 4 data	23/05/2022 7:03		Row 11 data
5	Row 5 data	23/05/2022 8:03		Row 15 data
6	Row 6 data	23/05/2022 5:03		Row 20 data
7	Row 7 data	23/05/2022 6:03
8	Row 8 data	23/05/2022 7:03
9	Row 9 data	23/05/2022 8:03
10	Row 10 data	23/05/2022 5:03
11	Row 11 data	23/05/2022 6:03
12	Row 12 data	23/05/2022 7:03
13	Row 13 data	23/05/2022 8:03
14	Row 14 data	23/05/2022 5:03
15	Row 15 data	23/05/2022 6:03
16	Row 16 data	23/05/2022 7:03
17	Row 17 data	23/05/2022 8:03
18	Row 18 data	23/05/2022 7:03
19	Row 19 data	23/05/2022 8:03
20	Row 20 data	23/05/2022 6:03
Check Time

Cell Formulas
Range	Formula
D2:D6	D2	=FILTER(A2:A20,(MOD(B2:B20,1)>=F2)*(MOD(B2:B20,1)<=F3),"")
Dynamic array formulas.

 

[nmk34]

I needed to use either function or VBA. the FILTER function is not in my excel 2010. I use excel 2010 at home and excel 365 at work. i wanted the results in column D altogether as the last example above. what will be the function in excel 2010?

 

[Peter_SSs]

what will be the function in excel 2010?

22 05 22.xlsm
	A	B	C	D	E	F
1	data	Date/time		target		Parametetrs
2	Row 2 data	23/05/2022 5:03		Row 3 data		6:00:00 AM
3	Row 3 data	23/05/2022 6:03		Row 7 data		7:00:00 AM
4	Row 4 data	23/05/2022 7:03		Row 11 data
5	Row 5 data	23/05/2022 8:03		Row 15 data
6	Row 6 data	23/05/2022 5:03		Row 20 data
7	Row 7 data	23/05/2022 6:03
8	Row 8 data	23/05/2022 7:03
9	Row 9 data	23/05/2022 8:03
10	Row 10 data	23/05/2022 5:03
11	Row 11 data	23/05/2022 6:03
12	Row 12 data	23/05/2022 7:03
13	Row 13 data	23/05/2022 8:03
14	Row 14 data	23/05/2022 5:03
15	Row 15 data	23/05/2022 6:03
16	Row 16 data	23/05/2022 7:03
17	Row 17 data	23/05/2022 8:03
18	Row 18 data	23/05/2022 7:03
19	Row 19 data	23/05/2022 8:03
20	Row 20 data	23/05/2022 6:03
Check Time (2)

Cell Formulas
Range	Formula
D2:D20	D2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$20)/((MOD(B$2:B$20,1)>=F$2)*(MOD(B$2:B$20,1)<=F$3)),ROWS(D$2:D2))),"")

 

[nmk34]

Why i am getting only one data point? can this function be used in VBA also?

 

[Alex Blakenburg]

1) You are only getting one data point because you only have the formula in 1 cell.
Copy the formula down to the rows below it (for more rows than you think you will ever need)

2) What is the 15,6
15 = the small function
6 = ignore errors
AGGREGATE function

 

[nmk34]

it worked, that was awesome thanks

 

[nmk34]

is there a way to make this function to accommodate two more conditions as shown below?