Need help calculating the longest streak in a date series

[SeanMulholland]

New to the forum. I appreciate any help that can be provided.

I have a worksheet of about 4000 rows that include dates and department data.

Example:

Department Event Date

Department 1 1/1/2009
Department 3 3/1/2009
Department 4 2/1/2009
Department 1 4/1/2009
Department 1 5/1/2009
etc....

I would like to build a formula on another sheet that evaluates the list of dates for each department and produces the number of days representing the longest streak for that department. In the example above for Department 1, I want to see the number of days between 1/1/2009 and 4/1/2009. As a longer streak develops for that department, I would like to see that number of dates appear and begin to grow.

I've looked over the forum, and found no obvious candidates.

Any thoughts?

[Richard Buttrey]

Hi,

I'm not clear how you are defining a 'streak'. Why for instance are you ignoring 5/1/2009. And what would a 'longer' streak for Dept 1 look like?

Does this simple example contain all permutation of the way data might be listed?

Finally, (and as I suspect) are those US date formats? i.e. M/D/Y with the last date being May 1st 2009 (rather than 5th Jan 2009). Over here we tend to use a different format, D/M/Y.

Regards

[SeanMulholland]

Yes, you are correct about the date format. It does represent M/D/YYYY.

In the previous example, the largest gap in dates is between 1/1/2009 and 4/1/2009. The 5/1/2009 date is ignored because the gap between 4/1/2009 and 5/1/2009 is not the largest gap in the data set. If a new date were to be added for Department 1, say 12/1/2009, the number of dates between 5/1/2009 and 12/1/2009 would be largest, and would therefore define the new longest streak. In thinking about it, I should also mention that I refresh the data once a week or so.

Other variables include:

- I seperately enter the date of the refresh, so this cell can be used to compare data to if the streak is current
- The data set is not necessary in date order, but I could make sure that it is
- I also need to filter the data based on a third variable (Flag)

Here is a different version of the example to clarify:

Refresh Date: 6/1/2009

Department Event Date Flag
Department 1 1/1/2009 A
Department 3 3/1/2009 B
Department 4 2/1/2009 A
Department 1 4/1/2009 B
Department 1 5/1/2009 A

In this version, the desired result for Department 1 be the largest number of days between Event Dates for occurances carrying the "A" Flag criteria (In this case, the number of dates between 1/1/2009 and 5/1/2009). If the data were to be refreshed on 12/1/2009, the largest streak would then be between 5/1/2009 and 12/1/2009.

The overall idea is to measure departmental performance in avoiding events that meet the "A" Flag criteria. I'm trying to do this for a data set of about 4000 rows which represent activity in 8 departments.