+ Reply to Thread
Results 1 to 4 of 4

Need to learn how to count consecutive hours in a list of dates...

  1. #1
    Registered User
    Join Date
    02-15-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question Need to learn how to count consecutive hours in a list of dates...

    I am working on something that requires me to count how many consecutive hours an evernt has occured over the last half year...
    Is there anyone that has an idea that could help me?

    The list consists of dates like:

    07/07/2011 19
    07/07/2011 20
    07/07/2011 21
    07/08/2011 07
    07/08/2011 08
    07/11/2011 07
    07/18/2011 05
    07/18/2011 06
    07/18/2011 07
    07/18/2011 08
    07/21/2011 07
    07/26/2011 07
    07/26/2011 08
    07/26/2011 09
    07/26/2011 10
    07/26/2011 11
    07/26/2011 12
    07/26/2011 13
    07/28/2011 09
    07/28/2011 12
    07/29/2011 09
    07/29/2011 10
    07/29/2011 12
    08/15/2011 08
    08/16/2011 10
    08/16/2011 11
    08/16/2011 12
    08/21/2011 12
    09/11/2011 08
    09/25/2011 11
    10/05/2011 08
    10/05/2011 09
    10/07/2011 11
    10/10/2011 09
    10/10/2011 12
    11/03/2011 05
    11/03/2011 06
    11/13/2011 08
    11/18/2011 05
    11/18/2011 06
    11/18/2011 07
    12/01/2011 13
    12/03/2011 08
    12/12/2011 06

    ideally I want this summary at the end of a list, if possible.

    16, 1 Hour Periods
    4, 2 Hour Periods
    3, 3 Hour Periods
    1, 4 Hour Period
    1, 7 Hour Period

    Please let me know if you would like any more details of clarification, I will gladdly supply anything for help.

  2. #2
    Registered User
    Join Date
    02-15-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: HELP! Need to learn how to count consecutive hours in a list of dates...

    I got the summary at the end by doing the counting manually. I need to figure out a formula or code or anything that i can plug into excel, and will allow me to make little (if any) changes from list to list, in order to get a similar summary for each list.

  3. #3
    Registered User
    Join Date
    02-15-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need to learn how to count consecutive hours in a list of dates...

    I have been able to figure out how to use multiple formulas which first allows me to convert all the consecutive hours into 1's and 0's using "=IF(OR(A3=A4-0.0416666666666,A3=A2+0.0416666666666),1,0)" and then I can count the consecutive 1's with "=INDEX(FREQUENCY(FREQUENCY(IF(B2:B45=0,ROW(B2:B45)),IF(B2:B45<>1,ROW(B2:B45))),{0,1}),2)". However I now run into the problem where it does not recognize the gap between 07/07/2011 21 and 07/08/2011 07, so it counts it as 1 5 hour incriment insted of 1 3 hour and 1 2 hour increments.


    If anyone has a better way of doing this I would gladly like to know.

    Thanks in advance, any help would greatly be appriciated.

    http://www.ozgrid.com/forum/showthread.php?t=162488

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Need to learn how to count consecutive hours in a list of dates...

    Why don't you upload a workbook with some of your data.

    Alf

+ 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