Return Value based on Date and Time

[uberathlete]

Hi everyone, I need some help. I asked a similar question earlier way back but this is a bit different. Here's the situation (please refer to image):

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date	Time	Price		Req Date	8:25:00 Price		Req Date	8:45:00 Price
2	1/1/00	8:25:00	5		1/1/00	5		1/1/00	65
3	1/1/00	8:30:00	15		1/2/00			1/2/00	2
4	1/1/00	8:45:00	65		1/5/00	69		1/5/00
5	1/1/00	12:00:05	78
6	1/2/00	9:30:05	2
7	1/2/00	10:00:00	33
8	1/2/00	10:30:00	65
9	1/2/00	10:35:00	48
10	1/2/00	10:40:00	79
11	1/5/00	7:00:00	85
12	1/5/00	8:00:00	100
13	1/5/00	8:25:00	69
14	1/5/00	8:30:00	99
Sheet1

I basically would like to fill columns F and I. For column F, the price that should show is the 8:25:00 price that corresponds to the date specified in column E. However, if there is no 8:25:00 price on that date, then the cell is left blank.

For column I, the price that should show is the 8:45:00 price that corresponds to the date specified in column H. However, if there is no 8:45:00 price, then use the price of the hour closest to but beyond 8:45:00. If neither condition is met, leave the cell blank.

So in this case, the result should be:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date	Time	Price		Req Date	8:25:00 Price		Req Date	8:45:00 Price
2	1/1/00	8:25:00	5		1/1/00	5		1/1/00	65
3	1/1/00	8:30:00	15		1/2/00			1/2/00	2
4	1/1/00	8:45:00	65		1/5/00	69		1/5/00
5	1/1/00	12:00:05	78
6	1/2/00	9:30:05	2
7	1/2/00	10:00:00	33
8	1/2/00	10:30:00	65
9	1/2/00	10:35:00	48
10	1/2/00	10:40:00	79
11	1/5/00	7:00:00	85
12	1/5/00	8:00:00	100
13	1/5/00	8:25:00	69
14	1/5/00	8:30:00	99
Sheet1

If anyone could help me out on this that would be great. Any suggestions would be much appreciated. Thanks!

 

[Beate Schmitz]

Hello,

formulas of cells F3 and I3 can be copied down:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date	Time	Price		Req Date	8:25:00		Req Date	8:45:00
2						Price			Price
3	1/1/2000	8:25:00	5		1/1/2000	5		1/1/2000	65
4	1/1/2000	8:30:00	15		2/1/2000			2/1/2000	2
5	1/1/2000	8:45:00	65		5/1/2000	69		5/1/2000
6	1/1/2000	12:00:05	78
7	2/1/2000	9:30:05	2
8	2/1/2000	10:00:00	33
9	2/1/2000	10:30:00	65
10	2/1/2000	10:35:00	48
11	2/1/2000	10:40:00	79
12	5/1/2000	7:00:00	85
13	5/1/2000	8:00:00	100
14	5/1/2000	8:25:00	69
15	5/1/2000	8:30:00	99
Sheet

 

[Aladin Akyurek]

Hi everyone, I need some help. I asked a similar question earlier way back but this is a bit different. Here's the situation (please refer to image):

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date	Time	Price		Req Date	8:25:00 Price		Req Date	8:45:00 Price
2	1/1/00	8:25:00	5		1/1/00	5		1/1/00	65
3	1/1/00	8:30:00	15		1/2/00			1/2/00	2
4	1/1/00	8:45:00	65		1/5/00	69		1/5/00
5	1/1/00	12:00:05	78
6	1/2/00	9:30:05	2
7	1/2/00	10:00:00	33
8	1/2/00	10:30:00	65
9	1/2/00	10:35:00	48
10	1/2/00	10:40:00	79
11	1/5/00	7:00:00	85
12	1/5/00	8:00:00	100
13	1/5/00	8:25:00	69
14	1/5/00	8:30:00	99
Sheet1

I basically would like to fill columns F and I. For column F, the price that should show is the 8:25:00 price that corresponds to the date specified in column E. However, if there is no 8:25:00 price on that date, then the cell is left blank.

For column I, the price that should show is the 8:45:00 price that corresponds to the date specified in column H. However, if there is no 8:45:00 price, then use the price of the hour closest to but beyond 8:45:00. If neither condition is met, leave the cell blank.

So in this case, the result should be:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date	Time	Price		Req Date	8:25:00 Price		Req Date	8:45:00 Price
2	1/1/00	8:25:00	5		1/1/00	5		1/1/00	65
3	1/1/00	8:30:00	15		1/2/00			1/2/00	2
4	1/1/00	8:45:00	65		1/5/00	69		1/5/00
5	1/1/00	12:00:05	78
6	1/2/00	9:30:05	2
7	1/2/00	10:00:00	33
8	1/2/00	10:30:00	65
9	1/2/00	10:35:00	48
10	1/2/00	10:40:00	79
11	1/5/00	7:00:00	85
12	1/5/00	8:00:00	100
13	1/5/00	8:25:00	69
14	1/5/00	8:30:00	99
Sheet1

If anyone could help me out on this that would be great. Any suggestions would be much appreciated. Thanks!

Book2
	A	B	C	D	E	F	G	H	I
1						8:25:00			8:45:00
2	Date	Time	Price		Req Date	Price		Req Date	Price
3	1/1/2000	8:25:00	5		1/1/2000	5		1/1/2000	65
4	1/1/2000	8:30:00	15		1/2/2000			1/2/2000	2
5	1/1/2000	8:45:00	65		1/5/2000	69		1/5/2000
6	1/1/2000	12:00:05	78
7	1/2/2000	9:30:05	2
8	1/2/2000	10:00:00	33
9	1/2/2000	10:30:00	65
10	1/2/2000	10:35:00	48
11	1/2/2000	10:40:00	79
12	1/5/2000	7:00:00	85
13	1/5/2000	8:00:00	100
14	1/5/2000	8:25:00	69
15	1/5/2000	8:30:00	99
16
Sheet1

Notice C1, which houses a simple formula...

=""

that allows us to return a "" in case the stipulated conditions are not met.

F3:

Control+shift+enter...

=INDEX($C$1:$C$15,MIN(IF($A$1:$A$15=E3,IF($B$1:$B$15=$F$1,ROW($A$1:$A$15)-ROW($A$1)+1))))

I3:

Control+shift+enter...

=INDEX($C$1:$C$15,MIN(IF($A$1:$A$15=$H3,IF($B$1:$B$15>=$I$1,IF(ISNUMBER($C$1:$C$15),ROW($A$1:$A$15)-ROW($A$1)+1)))))