Min if Function?

  • Thread starter Thread starter gb_S49
  • Start date Start date
G

gb_S49

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
 
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
 
Ahhh
Many Thanks

Teethless mama said:
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
Click to expand...
 
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.
 
Back
Top