Help with a date range function...

rizco

Board Regular
Joined
Aug 11, 2004
Messages
52
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.

Thanks in advance for all your help.
:pray:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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
Joined
Aug 11, 2004
Messages
52
Wouldn't that return the smallest date in February 2/1/2005 instead of the second smallest date 2/15/2005?
 

rizco

Board Regular
Joined
Aug 11, 2004
Messages
52
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
Joined
Feb 15, 2002
Messages
1,743
Yes, but it fails if you only have 1 entry for that month.
 

Forum statistics

Threads
1,147,697
Messages
5,742,688
Members
423,747
Latest member
Shadeslayers09

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top