About Us

Ron Rosenfeld

On Mon, 4 Jun 2007 13:47:00 -0700, Data Analyst <Data
wrote:

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!

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Earliest and latest dates that can be correctly represented in Exc	Kitten	Excel Discussion (Misc queries)	2	April 20th 07 05:16 AM
find latest date in a row, when dates have apostrophes in it	brakbek	Excel Discussion (Misc queries)	3	January 19th 07 09:14 PM
How do I find the earliest dates in a range of dates?	JJ	Excel Worksheet Functions	3	May 16th 06 09:36 AM
Find the earliest date in a range of dates?	Rachel Williams	Excel Worksheet Functions	2	February 10th 06 03:50 PM
Return the latest date from a list of dates	davidp	Excel Worksheet Functions	8	March 18th 05 12:48 AM

Menu

About Us