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

nmk34

New Member
Joined
Apr 12, 2022
Messages
45
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
ABCDEFG
1dataDate/timetargetParametetrs
2Row 2 data23/05/2022 5:03 6:00 AM
3Row 3 data23/05/2022 6:03Row 3 data7:00 AM
4Row 4 data23/05/2022 7:03 
5Row 5 data23/05/2022 8:03 
6Row 6 data23/05/2022 5:03 
7Row 7 data23/05/2022 6:03Row 7 data
8Row 8 data23/05/2022 7:03 
9Row 9 data23/05/2022 8:03 
10Row 10 data23/05/2022 5:03 
11Row 11 data23/05/2022 6:03Row 11 data
12Row 12 data23/05/2022 7:03 
13Row 13 data23/05/2022 8:03 
14Row 14 data23/05/2022 5:03 
15Row 15 data23/05/2022 6:03Row 15 data
16Row 16 data23/05/2022 7:03 
17Row 17 data23/05/2022 8:03 
18Row 18 data23/05/2022 7:03 
19Row 19 data23/05/2022 8:03 
20Row 20 data23/05/2022 6:03Row 20 data
21
Sheet1
Cell Formulas
RangeFormula
D2:D20D2=IF(AND(TIME(HOUR(B2),MINUTE(B2),)>=$F$2,TIME(HOUR(B2),MINUTE(B2),)<=$F$3),A2,"")
 
Upvote 0
If you wanted VBA to do this for you, assuming the same sheet layout as above, then:

VBA Code:
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
 
Upvote 0
Another possible interpretation of what is wanted.

22 05 22.xlsm
ABCDEF
1dataDate/timetargetParametetrs
2Row 2 data23/05/2022 5:03Row 3 data6:00:00 AM
3Row 3 data23/05/2022 6:03Row 7 data7:00:00 AM
4Row 4 data23/05/2022 7:03Row 11 data
5Row 5 data23/05/2022 8:03Row 15 data
6Row 6 data23/05/2022 5:03Row 20 data
7Row 7 data23/05/2022 6:03
8Row 8 data23/05/2022 7:03
9Row 9 data23/05/2022 8:03
10Row 10 data23/05/2022 5:03
11Row 11 data23/05/2022 6:03
12Row 12 data23/05/2022 7:03
13Row 13 data23/05/2022 8:03
14Row 14 data23/05/2022 5:03
15Row 15 data23/05/2022 6:03
16Row 16 data23/05/2022 7:03
17Row 17 data23/05/2022 8:03
18Row 18 data23/05/2022 7:03
19Row 19 data23/05/2022 8:03
20Row 20 data23/05/2022 6:03
Check Time
Cell Formulas
RangeFormula
D2:D6D2=FILTER(A2:A20,(MOD(B2:B20,1)>=F2)*(MOD(B2:B20,1)<=F3),"")
Dynamic array formulas.
 
Upvote 0
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?
 
Upvote 0
what will be the function in excel 2010?

22 05 22.xlsm
ABCDEF
1dataDate/timetargetParametetrs
2Row 2 data23/05/2022 5:03Row 3 data6:00:00 AM
3Row 3 data23/05/2022 6:03Row 7 data7:00:00 AM
4Row 4 data23/05/2022 7:03Row 11 data
5Row 5 data23/05/2022 8:03Row 15 data
6Row 6 data23/05/2022 5:03Row 20 data
7Row 7 data23/05/2022 6:03 
8Row 8 data23/05/2022 7:03 
9Row 9 data23/05/2022 8:03 
10Row 10 data23/05/2022 5:03 
11Row 11 data23/05/2022 6:03 
12Row 12 data23/05/2022 7:03 
13Row 13 data23/05/2022 8:03 
14Row 14 data23/05/2022 5:03 
15Row 15 data23/05/2022 6:03 
16Row 16 data23/05/2022 7:03 
17Row 17 data23/05/2022 8:03 
18Row 18 data23/05/2022 7:03 
19Row 19 data23/05/2022 8:03 
20Row 20 data23/05/2022 6:03 
Check Time (2)
Cell Formulas
RangeFormula
D2:D20D2=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))),"")
 
Upvote 0
1653239476232.png

Why i am getting only one data point? can this function be used in VBA also?
 
Upvote 0
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
 
Upvote 0
is there a way to make this function to accommodate two more conditions as shown below?
1653262390754.png
 
Upvote 0

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

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