Min if Function?
 

Hi,
I am trying to create a formulae that will report back the Minimum days
between 2 date ranges without any success...can anyone advise?
(eg min days between 9th July & 24th july would be 10)
DATE Days
09/07/08 10
21/07/08 22
24/07/08 22
06/08/08 22
06/08/08 22
07/08/08 100
22/10/08 20
20/08/08 23
04/09/08 23
18/11/08 20
18/11/08 17
11/11/08 20
17/09/08 22

Min if Function?
 

Data in A2:B14
D1: holds early date
E1: holds later date

=MIN(INDEX(10^10-(A2:A14=D1)*(A2:A14<=E1)*(10^10-B2:B14),))

Just press ENTER



"gb_S49" wrote:

Hi,
I am trying to create a formulae that will report back the Minimum days
between 2 date ranges without any success...can anyone advise?
(eg min days between 9th July & 24th july would be 10)
DATE Days
09/07/08 10
21/07/08 22
24/07/08 22
06/08/08 22
06/08/08 22
07/08/08 100
22/10/08 20
20/08/08 23
04/09/08 23
18/11/08 20
18/11/08 17
11/11/08 20
17/09/08 22

Min if Function?
 

Ahhh
Many Thanks

"Teethless mama" wrote:

Data in A2:B14
D1: holds early date
E1: holds later date

=MIN(INDEX(10^10-(A2:A14=D1)*(A2:A14<=E1)*(10^10-B2:B14),))

Just press ENTER



"gb_S49" wrote:

Hi,
I am trying to create a formulae that will report back the Minimum days
between 2 date ranges without any success...can anyone advise?
(eg min days between 9th July & 24th july would be 10)
DATE Days
09/07/08 10
21/07/08 22
24/07/08 22
06/08/08 22
06/08/08 22
07/08/08 100
22/10/08 20
20/08/08 23
04/09/08 23
18/11/08 20
18/11/08 17
11/11/08 20
17/09/08 22

Min if Function?
 

You're Welcome!

"gb_S49" wrote:

Ahhh
Many Thanks

"Teethless mama" wrote:

Data in A2:B14
D1: holds early date
E1: holds later date

=MIN(INDEX(10^10-(A2:A14=D1)*(A2:A14<=E1)*(10^10-B2:B14),))

Just press ENTER



"gb_S49" wrote:

Hi,
I am trying to create a formulae that will report back the Minimum days
between 2 date ranges without any success...can anyone advise?
(eg min days between 9th July & 24th july would be 10)
DATE Days
09/07/08 10
21/07/08 22
24/07/08 22
06/08/08 22
06/08/08 22
07/08/08 100
22/10/08 20
20/08/08 23
04/09/08 23
18/11/08 20
18/11/08 17
11/11/08 20
17/09/08 22

Min if Function?
 

The shorter array** version is slightly more efficient:

=MIN(IF((A2:A14=D1)*(A2:A14<=E1),B2:B14))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Data in A2:B14
D1: holds early date
E1: holds later date

=MIN(INDEX(10^10-(A2:A14=D1)*(A2:A14<=E1)*(10^10-B2:B14),))

Just press ENTER



"gb_S49" wrote:

Hi,
I am trying to create a formulae that will report back the Minimum days
between 2 date ranges without any success...can anyone advise?
(eg min days between 9th July & 24th july would be 10)
DATE Days
09/07/08 10
21/07/08 22
24/07/08 22
06/08/08 22
06/08/08 22
07/08/08 100
22/10/08 20
20/08/08 23
04/09/08 23
18/11/08 20
18/11/08 17
11/11/08 20
17/09/08 22