+ Reply to Thread
Results 1 to 7 of 7

Finding maximum number of consecutive days

  1. #1
    Registered User
    Join Date
    08-01-2011
    Location
    Denver, CO
    MS-Off Ver
    Excel 2016
    Posts
    23

    Finding maximum number of consecutive days

    I have a spreadsheet of groups of dates. Within a group of dates I want to find the maximum number of days in that list.

    Example: In this list of dates I want to know that the max # of consecutive dates is 3. Any ideas? Thanks for your help.
    4/3/2019
    4/5/2019
    4/7/2019
    4/10/2019
    4/12/2019
    4/14/2019
    4/17/2019
    4/19/2019
    4/20/2019
    4/21/2019

    4/24/2019
    4/28/2019

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Finding maximum number of consecutive days

    a
    b
    c
    2
    4/3/2019
    3
    b2: {=max(frequency(if(a3:a13 = a2:a12 + 1, row(a3:a13)), if(a3:a13 <> a2:a12 + 1, row(a3:a13)))) + 1}
    3
    4/5/2019
    4
    4/7/2019
    5
    4/10/2019
    6
    4/12/2019
    7
    4/14/2019
    8
    4/17/2019
    9
    4/19/2019
    10
    4/20/2019
    11
    4/21/2019
    12
    4/24/2019
    13
    4/28/2019
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,765

    Re: Finding maximum number of consecutive days

    If dates starts in A1, put into B1:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    accept with Ctrl+Shift+Enter.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  4. #4
    Registered User
    Join Date
    08-01-2011
    Location
    Denver, CO
    MS-Off Ver
    Excel 2016
    Posts
    23

    Re: Finding maximum number of consecutive days

    Thank you both for your answers. I've tried both and am not having any luck. I can't figure out what is wrong. I've copy/pasted them both in and typed them exactly and it's not working. If I type in other formulas or functions on the sheet those do work. I've attached my sheet for reference. Thank you!
    Attached Files Attached Files

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Finding maximum number of consecutive days

    You can't type in the curly braces in an array formula.

    Paste the formula in the formula bar, press and hold the Ctrl and Shift keys, then press Enter. That will make them appear.

  6. #6
    Registered User
    Join Date
    08-01-2011
    Location
    Denver, CO
    MS-Off Ver
    Excel 2016
    Posts
    23

    Re: Finding maximum number of consecutive days

    Sorry, I meant to say I did do that as well.

  7. #7
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,765

    Re: Finding maximum number of consecutive days

    Both works.
    From your last attachment:
    in B2 (mine formula) has not been accepted with Ctrl+Shift+Enter, accept with CSE and it gives you 2.
    in F2 (shg formula) curly brackets probably added manually, delete them (at start / at end) accept with CSE (as above) and it gives you 2.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Maximum cumulative value for the consecutive 3 days of data in 1 year period
    By bennyistanto in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-26-2017, 08:43 AM
  2. Consecutive 5 Maximum number in a column
    By ssb648 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2016, 03:12 AM
  3. Replies: 5
    Last Post: 03-23-2015, 07:06 AM
  4. [SOLVED] Counting maximum number of consecutive missing values
    By Moriexcel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2014, 04:45 AM
  5. Counting Maximum Consecutive Days
    By Delleeb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-26-2013, 03:51 PM
  6. [SOLVED] count the maximum number consecutive
    By Berna11 in forum Excel General
    Replies: 9
    Last Post: 07-13-2012, 11:27 AM
  7. Finding the maximum occurrence of text over non-consecutive cells
    By raehippychick in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2009, 09:04 AM

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