+ Reply to Thread
Results 1 to 3 of 3

Need help calculating the longest streak in a date series

  1. #1
    Registered User
    Join Date
    01-11-2010
    Location
    Ft. Worth, TX
    MS-Off Ver
    Excel 2007
    Posts
    11

    Cool Need help calculating the longest streak in a date series

    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?

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,957

    Re: Need help calculating the longest streak in a date series

    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
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-11-2010
    Location
    Ft. Worth, TX
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Need help calculating the longest streak in a date series

    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.
    Last edited by SeanMulholland; 01-12-2010 at 10:54 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1