Calculating earliest start and latest end dates by contract

Analyst@discussions.microsoft.com Data Analyst

unread,

Jun 4, 2007, 4:47:00 PM6/4/07

to

Hi everyone,

I went through some of the questions and have not been successful at finding
the simplest and concise way of tracking contracts for my reporting purposes.
Currently, I track multiple tasks for one contract number. As a result, I end
up have multiple task dates for one project that I will need to summarize
into a monthly report. At this point, I have been doing it manually and has
been quite time consuming. I am hoping there is a faster way for me to get
the same results.

Here is an example of what I'm after. I have a report that looks like the
example below:

Contract Task Start Date Task End Date
101010 01/05/99 12/3/05
101010 12/8/04 11/5/07
356 08/06/05 4/8/07
12345 11/3/89 11/01/01
12345 10/6/01 12/4/07

Now with that said, what are the steps I should take and the formula I
should use to get a result similar to this kind:

Contract Task Start Date Task End Date
101010 01/05/99 11/5/07
356 08/06/05 4/8/07
12345 11/3/89 12/4/07

Any help is GREATLY appreciated!

T. Valko

unread,

Jun 4, 2007, 5:23:18 PM6/4/07

to

With this data in the range A2:C6

> 101010 01/05/99 12/3/05
> 101010 12/8/04 11/5/07
> 356 08/06/05 4/8/07
> 12345 11/3/89 11/01/01
> 12345 10/6/01 12/4/07

A10 = 101010

Try these array formulas**

For the min start date:

=MIN(IF(A2:A6=A11,B2:B6))

For the max end date:

=MAX(IF(A2:A6=A11,C2:C6))

Format as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Data Analyst" <Data Ana...@discussions.microsoft.com> wrote in message
news:F80F8EFC-2C69-4C08...@microsoft.com...

bj

unread,

Jun 4, 2007, 5:36:02 PM6/4/07

to

I may have lost my reply
select contract_list
use <data><filter><advanced filter> { unique list} to get a unique contract
list
if you put it in Column D with the lable in D1
in E1 enter "TaskStart Date"
in E2 enter
=Min(if(Contract_range=D2,Task_Start_Date_range,max(Task_Start_Date_range)))
enter as an array control-shirft-enter
copy down to the end of the unique list
in F2 enter
=Mmax(if(Contract_range=D2,Task_end_Date_range,min(Task_end_Date_range)))
again enter as an array function and copy down

T. Valko

unread,

Jun 4, 2007, 5:46:14 PM6/4/07

to

Typo:

Both formulas should reference cell A10, not A11.

Biff

"T. Valko" <biffi...@comcast.net> wrote in message
news:uhQSV6up...@TK2MSFTNGP06.phx.gbl...

Ron Rosenfeld

unread,

Jun 4, 2007, 6:23:33 PM6/4/07

to

You can also use a Pivot Table.

Drag Contract to the Rows area.

Drag Task Start Date to the Data area
Set the Field Settings to Min
Name it to something like Start Date
Drag Task End Date to the Data area.
Set the Field Settings to Max
Name it End Date

Under Table options, deselect Grand Totals for Columns (and Rows)

Choose one of the available formats.

Here is what I got with your data:

Contract Start Date End Date
356 8/6/2005 4/8/2007
12345 11/3/1989 12/4/2007
101010 1/5/1999 11/5/2007

You can sort by the different columns, or move the data using the Order menu:

Contract Start Date End Date
101010 1/5/1999 11/5/2007
356 8/6/2005 4/8/2007
12345 11/3/1989 12/4/2007

Format the "dates" as Dates

--ron