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

 

[Peter_SSs]

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

=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)*(A$2:A$20>=1134)*(A$2:A$20<=1149)),ROWS(D$2:D2))),"")

 

[nmk34]

Thanks a lot it works great

 

[nmk34]

just one more thing, is it possible to list each data in the target with its matching time stamp in a separate column? i want to chart the data with the correct time

 

[Peter_SSs]

22 05 22.xlsm
	A	B	C	D	E	F	G
1	data	Date/time		data	Date/time		Parametetrs
2	1134	23/05/2022 5:03		1140	23/05/2022 6:03		6:00:00 AM
3	1140	23/05/2022 6:03		1145	23/05/2022 6:03		7:00:00 AM
4	1142	23/05/2022 7:03		1135	23/05/2022 6:03
5	1136	23/05/2022 8:03
6	1146	23/05/2022 5:03
7	1145	23/05/2022 6:03
8	1152	23/05/2022 7:03
9	1150	23/05/2022 8:03
10	1152	23/05/2022 5:03
11	1132	23/05/2022 6:03
12	1136	23/05/2022 7:03
13	1152	23/05/2022 8:03
14	1149	23/05/2022 5:03
15	1151	23/05/2022 6:03
16	1145	23/05/2022 7:03
17	1148	23/05/2022 8:03
18	1151	23/05/2022 7:03
19	1138	23/05/2022 8:03
20	1135	23/05/2022 6:03
Check Time (3)

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)>=G$2)*(MOD(B$2:B$20,1)<=G$3)*(A$2:A$20>=1134)*(A$2:A$20<=1149)),ROWS(D$2:D2))),"")
E2:E20	E2	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$20)/((MOD(B$2:B$20,1)>=G$2)*(MOD(B$2:B$20,1)<=G$3)*(A$2:A$20>=1134)*(A$2:A$20<=1149)),ROWS(B$2:B2))),"")

 

[nmk34]

I posting under the same question because i am using in the attachment the formulas above by peter_SSs.
i am using this formula to extract the data from column B and the time stamp from column C.
The creterias:
1) time from now 5minutes, 30minutes,1hour,4hours and 8hours.
2) minimum and max limits
the formulas as in the excel workbook are working, the problem, excel is using a long time to do the calculations.
the workbook is stripped from all other sensitive information to focus on this issue. two buttons on the sheet, one to clear the data columns b and c. the other one generate a new incoming data and time stamp. is there a better way or modify the existing code to make the calculation faster?

 

[nmk34]

Averageanaylsis2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	DATA IN COLUMN #	2			Time	Weight(1 hour ago)	Target				Current Time	1 Hour Ago	30 Min Ago	4 Hours Ago	8 Hours Ago	5 Min Ago	20	21	22
2	TIME IN COLUMN #	1/3/1900	3								5/28/2022 18:56	17:56:29	18:26:29	14:56:29	10:56:29	18:51:29
3	ROW #	Weight DATA	TIME STAMP		=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$4:C$3000)/((MOD(C$4:C$3000,1)>=$L$3)*(MOD(C$4:C$3000,1)<=$K$3)*(B$4:B$3000>=$K$6)*(B$4:B$3000<=$L$6)),ROWS(B$4:B5))),"")	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$4:B$3000)/((MOD(C$4:C$3000,1)>=L$3)*(MOD(C$4:C$3000,1)<=K$3)*(B$4:B$3000>=K$6)*(B$4:B$3000<=L$6)),ROWS(F$2:F3))),"")	0				18:56:28	17:56:29	18:26:29	14:56:29	10:56:29	18:51:29
4											#N/A
5											min	max
6											907	917
7
8											TIME(30MIN)	Weight(30 MIN ago)	TIME(4HRS)	Weight(4hrs ago)	TIME(8HRS)	Weight(8hrs ago)	TIME(5MIN)	Weight(5 MIN ago)
9											=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$4:C$3000)/((MOD(C$4:C$3000,1)>=$M$3)*(MOD(C$4:C$3000,1)<=$K$3)*(B$4:B$3000>=$K$6)*(B$4:B$3000<=$L$6)),ROWS(B$4:B4))),"")	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$4:B$3000)/((MOD(C$4:C$3000,1)>=M$3)*(MOD(C$4:C$3000,1)<=K$3)*(B$4:B$3000>=K$6)*(B$4:B$3000<=L$6)),ROWS(L$9:L9))),"")	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$4:C$3000)/((MOD(C$4:C$3000,1)>=$N$3)*(MOD(C$4:C$3000,1)<=$K$3)*(B$4:B$3000>=$K$6)*(B$4:B$3000<=$L$6)),ROWS(B$4:B4))),"")	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$4:B$3000)/((MOD(C$4:C$3000,1)>=N$3)*(MOD(C$4:C$3000,1)<=K$3)*(B$4:B$3000>=K$6)*(B$4:B$3000<=L$6)),ROWS(N$9:N9))),"")	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$4:C$3000)/((MOD(C$4:C$3000,1)>=$O$3)*(MOD(C$4:C$3000,1)<=$K$3)*(B$4:B$3000>=$K$6)*(B$4:B$3000<=$L$6)),ROWS(B$4:B4))),"")	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$4:B$3000)/((MOD(C$4:C$3000,1)>=O$3)*(MOD(C$4:C$3000,1)<=K$3)*(B$4:B$3000>=K$6)*(B$4:B$3000<=L$6)),ROWS(O$9:O9))),"")	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$4:C$3000)/((MOD(C$4:C$3000,1)>=$P$3)*(MOD(C$4:C$3000,1)<=$K$3)*(B$4:B$3000>=$K$6)*(B$4:B$3000<=$L$6)),ROWS(B$4:B4))),"")	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$4:B$3000)/((MOD(C$4:C$3000,1)>=P$3)*(MOD(C$4:C$3000,1)<=K$3)*(B$4:B$3000>=K$6)*(B$4:B$3000<=L$6)),ROWS(P$9:P9))),"")
10
11
12
13
14
15
16
17
18
Sheet1

Cell Formulas
Range	Formula
E2	E2	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$4:C$3000)/((MOD(C$4:C$3000,1)>=$L$3)*(MOD(C$4:C$3000,1)<=$K$3)*(B$4:B$3000>=$K$6)*(B$4:B$3000<=$L$6)),ROWS(B$4:B4))),"")
F2	F2	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$4:B$3000)/((MOD(C$4:C$3000,1)>=L$3)*(MOD(C$4:C$3000,1)<=K$3)*(B$4:B$3000>=K$6)*(B$4:B$3000<=L$6)),ROWS(F$2:F2))),"")
L2	L2	=NOW()-1/24
M2	M2	=NOW()-1/24/2
N2	N2	=NOW()-4/24
O2	O2	=NOW()-8/24
P2	P2	=NOW()-1/24/2/6
G3	G3	=IF(F3="","",G2)
K3:P3	K3	=TIME(HOUR(K2),MINUTE(K2),SECOND(K2))
K4	K4	=INDEX(X2:X9,MATCH(K6,Y2:Y9,0))

Named Ranges
Name	Refers To	Cells
ChartTarget	=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G) -1,1)	G3
ChartValues	=OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F:$F) -1,1)	F2

 

[nmk34]

i solved this problem with inclosing the formulas in if statements.

 

[Peter_SSs]

Thanks for letting us know.