Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |