Hi,
So I change you sample data to the table below and have E110 in C1. My
formula returns
13 - Jan - 2002
i.e the earliest dat for project E110
If your getting 0 then are you sure you array entered the formula correctly?
Read these instructions again and enter the formula as an array
This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
13-Jan-2006 R102 E110
13-Jan-2006 R104
13-Jan-2006 R105
13-Jan-2006 R111
13-Jan-2006 R112
13-Jan-2006 R113
13-Jan-2006 R130
13-Jan-2006 E114
13-Jan-2006 E110
13-Jan-2002 E110
13-Jan-2006 E110
Mike
"MCscrewdriver" wrote:
> Thanks Mike,
> Unfortunately this array formula only returns 0 so i think it must be down
> to my initial explanation - i'll try again.
>
> this is an excerpt of the table:
>
> PA date Project
> 13-Jan-2006 R102
> 13-Jan-2006 R104
> 13-Jan-2006 R105
> 13-Jan-2006 R111
> 13-Jan-2006 R112
> 13-Jan-2006 R113
> 13-Jan-2006 R130
> 13-Jan-2006 E114
> 13-Jan-2006 E110
> 13-Jan-2006 E110
> 13-Jan-2006 E110
>
> Projects may appear several times in any period due to a number of employees
> booking time in the same week. In subsequent weeks the same projects will be
> booked to and i need to determine the first time each project was booked to.
> Hope this is clearer.
>
>
>
> "Mike H" wrote:
>
> > Hi,
> >
> > Try this with the project number your looking for in C1, change the ranges
> > to suit your data but they must all be the same size.
> >
> > =MIN(IF(B1:B20=C1,IF(A1:A20<>"",A1:A20)))
> >
> >
> > This is an array formula which must be entered by pressing CTRL+Shift+Enter
> > 'and not just Enter. If you do it correctly then Excel will put curly brackets
> > 'around the formula {}. You can't type these yourself. If you edit the formula
> > 'you must enter it again with CTRL+Shift+Enter.
> >
> > Mike
> >
> > "MCscrewdriver" wrote:
> >
> > > Probably dead easy but its got me stumped.
> > > Each week hours are booked to a number of different projects by 20 or more
> > > people. I need to identify the first date any time was booked to a particular
> > > project.
> > > Column A is the list of dates and column B is the project reference number.
> > > neither of the columns are in order.
> > > once i get this sorted i then need to use different tables of rates
> > > depending on the start date to calculate project costs (but more of that
> > > later)
|
Similar Threads
