# Return Value based on Date and Time

#### uberathlete

##### Board Regular
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
ABCDEFGHI
1DateTimePriceReq Date8:25:00 PriceReq Date8:45:00 Price
21/1/008:25:0051/1/0051/1/0065
31/1/008:30:00151/2/001/2/002
41/1/008:45:00651/5/00691/5/00
51/1/0012:00:0578
61/2/009:30:052
71/2/0010:00:0033
81/2/0010:30:0065
91/2/0010:35:0048
101/2/0010:40:0079
111/5/007:00:0085
121/5/008:00:00100
131/5/008:25:0069
141/5/008:30:0099
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
ABCDEFGHI
1DateTimePriceReq Date8:25:00 PriceReq Date8:45:00 Price
21/1/008:25:0051/1/0051/1/0065
31/1/008:30:00151/2/001/2/002
41/1/008:45:00651/5/00691/5/00
51/1/0012:00:0578
61/2/009:30:052
71/2/0010:00:0033
81/2/0010:30:0065
91/2/0010:35:0048
101/2/0010:40:0079
111/5/007:00:0085
121/5/008:00:00100
131/5/008:25:0069
141/5/008:30:0099
Sheet1

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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Beate Schmitz

##### Active Member
Hello,

formulas of cells F3 and I3 can be copied down:
Excel Workbook
ABCDEFGHI
1DateTimePriceReq Date8:25:00Req Date8:45:00
2PricePrice
31/1/20008:25:0051/1/200051/1/200065
41/1/20008:30:00152/1/20002/1/20002
51/1/20008:45:00655/1/2000695/1/2000
61/1/200012:00:0578
72/1/20009:30:052
82/1/200010:00:0033
92/1/200010:30:0065
102/1/200010:35:0048
112/1/200010:40:0079
125/1/20007:00:0085
135/1/20008:00:00100
145/1/20008:25:0069
155/1/20008:30:0099
Sheet

##### MrExcel MVP
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
ABCDEFGHI
1DateTimePriceReq Date8:25:00 PriceReq Date8:45:00 Price
21/1/008:25:0051/1/0051/1/0065
31/1/008:30:00151/2/001/2/002
41/1/008:45:00651/5/00691/5/00
51/1/0012:00:0578
61/2/009:30:052
71/2/0010:00:0033
81/2/0010:30:0065
91/2/0010:35:0048
101/2/0010:40:0079
111/5/007:00:0085
121/5/008:00:00100
131/5/008:25:0069
141/5/008:30:0099
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
ABCDEFGHI
1DateTimePriceReq Date8:25:00 PriceReq Date8:45:00 Price
21/1/008:25:0051/1/0051/1/0065
31/1/008:30:00151/2/001/2/002
41/1/008:45:00651/5/00691/5/00
51/1/0012:00:0578
61/2/009:30:052
71/2/0010:00:0033
81/2/0010:30:0065
91/2/0010:35:0048
101/2/0010:40:0079
111/5/007:00:0085
121/5/008:00:00100
131/5/008:25:0069
141/5/008:30:0099
Sheet1

If anyone could help me out on this that would be great. Any suggestions would be much appreciated. Thanks!
Book2
ABCDEFGHI
1 8:25:008:45:00
2DateTimePriceReq DatePriceReq DatePrice
31/1/20008:25:0051/1/200051/1/200065
41/1/20008:30:00151/2/2000 1/2/20002
51/1/20008:45:00651/5/2000691/5/2000
61/1/200012:00:0578
71/2/20009:30:052
81/2/200010:00:0033
91/2/200010:30:0065
101/2/200010:35:0048
111/2/200010:40:0079
121/5/20007:00:0085
131/5/20008:00:00100
141/5/20008:25:0069
151/5/20008:30:0099
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)))))

Replies
7
Views
341
Replies
6
Views
176
Replies
1
Views
332
Replies
6
Views
275
Replies
2
Views
264

1,195,619
Messages
6,010,733
Members
441,567
Latest member
Flitbee

### 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.

### Which adblocker are you using?

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

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