Help with a date range function...

[rizco]

Test.xls
	A	B	C	D	E	F	G	H
1	Dates	1/12/2005	1/27/2005	2/1/2005	2/15/2005	2/28/2005	3/12/2005	3/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.

Thanks in advance for all your help.
ray:

 

[IML]

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]

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

 

[IML]

Yes. I misread it. Let me play a bit more.

 

[rizco]

Hmmmm.

This seemed to work.

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

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

 

[IML]

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