# Find Max and Min Values between dates

#### RickyF

##### New Member
I'm trying to find the highest and lowest values between two dates.

Spreadsheet is configured with the columns Date, Time, Open, High, Low & Close. Rows contain Dates are between 1-Jan-2014 to present for each date there are 24 entries for each hours of the day. What I'm trying to find out is the highest value from the High column and the lowest value from the Low column between two dates and times.

For example, I would like to find out what is the highest and lowest values between 27/05/2014 at 06:00 to 28/05/2014 at 06:00. I've thought about using Lookup, Offset, Index and Match but from what I have read these require a range between where to search. As I'm first searching for the range before returning a value this where I'm stuck.

I'm completely stumped as to where to start with any kind of formula or VBA script that will bring me results, therefore any help is appreciated.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Where are you going to store 27/05/2014 etc? In some cells? Or in the formula?

Where are you going to store 27/05/2014 etc? In some cells? Or in the formula?

Steve
The intention is to extract the data into some cells onto a seperate worksheet.

Welcome to MrExcel.

Example:

Excel 2010
ABCDEF
1DateTimeHighLowStart27/05/2014 06:00
225/05/201406:00:00217212End28/05/2014 06:00
325/05/201412:00:00300295MaxHigh286
425/05/201418:00:00214209MinLow211
526/05/201400:00:00256251
626/05/201406:00:00219214
726/05/201412:00:00228223
826/05/201418:00:00295290
927/05/201400:00:00297292
1027/05/201406:00:00249244
1127/05/201412:00:00216211
1227/05/201418:00:00222217
1328/05/201400:00:00286281
1428/05/201406:00:00279274
1528/05/201412:00:00274269
1628/05/201418:00:00228223
1729/05/201400:00:00212207
1829/05/201406:00:00209204
Sheet1
Cell Formulas
RangeFormula
F3{=MAX(IF((A2:A18+B2:B18)>=F1,IF((A2:A18+B2:B18)<=F2,C2:C18)))}
F4{=MIN(IF((A2:A18+B2:B18)>=F1,IF((A2:A18+B2:B18)<=F2,D2:D18)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

max:
arrayformula:

=MAX((A2:A18>F1)*(A2:A18 < F2)*(C2:C18))

Last edited by a moderator:
Welcome to MrExcel.

Example:

Excel 2010
ABCDEF
1DateTimeHighLowStart27/05/2014 06:00
225/05/201406:00:00217212End28/05/2014 06:00
325/05/201412:00:00300295MaxHigh286
425/05/201418:00:00214209MinLow211
526/05/201400:00:00256251
626/05/201406:00:00219214
726/05/201412:00:00228223
826/05/201418:00:00295290
927/05/201400:00:00297292
1027/05/201406:00:00249244
1127/05/201412:00:00216211
1227/05/201418:00:00222217
1328/05/201400:00:00286281
1428/05/201406:00:00279274
1528/05/201412:00:00274269
1628/05/201418:00:00228223
1729/05/201400:00:00212207
1829/05/201406:00:00209204

</tbody>
Sheet1

Array Formulas
CellFormula
F3{=MAX(IF((A2:A18+B2:B18)>=F1,IF((A2:A18+B2:B18)<=F2,C2:C18)))}
F4{=MIN(IF((A2:A18+B2:B18)>=F1,IF((A2:A18+B2:B18)<=F2,D2:D18)))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks Andrew works a treat. looks like I need to work on my array formulas.

Replies
8
Views
323
Replies
0
Views
222
Replies
7
Views
352
Replies
6
Views
1K
Replies
2
Views
369

1,221,239
Messages
6,158,716
Members
451,510
Latest member
kegnazmach

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