# Help with a date range function...

#### rizco

##### Board Regular
Test.xls
ABCDEFGH
1Dates1/12/20051/27/20052/1/20052/15/20052/28/20053/12/20053/27/2005
Sheet1

In the example above I have a date range.
Lets say I am looking to display the second smallest date in February 2005 (2/15/2005)
If all the dates were in Feb, I would just do the formula [=SMALL(D1:F1,2)]
but they are not.
I would like to enter in a date in say cell Z1 that says 2/1/2005.
I would like cell A2 to return a result that goes something like this:
=SMALL(B1:H1),2 where Month and Year= Z1
returning the second smallest date during the month and year specified in cell Z1.

ray:

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### IML

##### MrExcel MVP
For non year specific you could use
=MIN(IF(MONTH(A1:G1)=2,A1:G1))
(where 2 represents February)

For year specific, try
=MIN(IF((A1:G1>=--"2-1-05")*(A1:G1<=--("2-28-25")),A1:G1))

Both formulas require array (control shift enter) confirmation

#### rizco

##### Board Regular
Wouldn't that return the smallest date in February 2/1/2005 instead of the second smallest date 2/15/2005?

#### IML

##### MrExcel MVP
Yes. I misread it. Let me play a bit more.

#### rizco

##### Board Regular
Hmmmm.

This seemed to work.

=SMALL(IF(MONTH(B1:H1)=2,B1:H1),2)

Of course, it requires the (control shift enter) confirmation

#### IML

##### MrExcel MVP
Yes, but it fails if you only have 1 entry for that month.

Replies
2
Views
69
Replies
5
Views
160
Replies
5
Views
104
Replies
2
Views
336
Replies
3
Views
639

1,181,994
Messages
5,933,164
Members
436,883
Latest member
RyanI1986

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