Thanks a lot, it works. I changed the formula a little bit, so I can copy the
formula without changing the dates every time.
F G H I
1
2 2003
3 Q1 Q2 Q3 Q4
4 01-01-03 01-04-03 01-07-03 01-10-03
5 31-03-03 30-06-03 30-09-03 31-12-03
6 9:54 10:15 10:45 12:45
=IF(TODAY()>F5;MIN(IF(($C$2:$C$25>=DATE(YEAR(F4);MONTH(F4);DAY(F4)))*($C$2:$C$25<=DATE(YEAR(F5);MONTH(F5);DAY(F5)));$D$2:$D$25));"")
Mark
"RagDyer" schreef:
> One way would be to hard code your quarters into 4 separate formulas.
>
> How about entering Q1, Q2, Q3, Q4, in E1 to E4.
>
> Then enter this *array* formulas in F1:
>
> =MIN(IF((C2:C100>=DATEVALUE("2003/1/1"))*(C2:C100<=DATEVALUE("2003/3/31")),D
> 2:D100))
>
> --
> Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
> regular <Enter>, which will *automatically* enclose the formula in curly
> brackets, which *cannot* be done manually.
>
> Copy the formula down to F4, and change the dates.
>
> Array formulas must also be entered with CSE even after revisions.
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Mark Rugers" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I want to find the minimum time in which a batch has been produced for
> each
> > quarter of a year. I have tried different formulas (included arrays +
> > Control/Shift/Enter) with unsatisfied results. Below is a simplified
> example
> > for the year 2003.
> >
> > A B C D
> > 1 Year Quarter Date Duration of batch
> > 2 2003 Q1 01-02-03 13:45
> > 3 02-02-03 12:45
> > 4 10-03-03 9:54
> > 5 2003 Q2 11-04-03 11:24
> > 6 24-05-03 11:00
> > 7 24-06-03 10:15
> > 8 2003 Q3 14-07-03 11:15
> > 9 14-07-03 10:45
> > 10 02-08-03 16:30
> > 11 2003 Q4 02-11-03 15:00
> > 12 22-11-03 12:45
> > 13 23-12-03 13:00
> >
> > I hope this makes sense.
> >
> > Kind regards
> >
> > Mark
>
>
Bookmarks