+ Reply to Thread
Results 1 to 7 of 7

Count instance of >8 days between consecutive dates in a list of dates

  1. #1
    Registered User
    Join Date
    12-07-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    26

    Count instance of >8 days between consecutive dates in a list of dates

    A1:E1 = 1,2,3,4,5
    A2:E2 = 1/1/17,1/8/17,1/17/17,1/23/17,2/2/17

    So I have an index row, and a row of dates beneath it. I want to get a count of the number of times that there are more than 8 days in between 2 consecutive dates. I can accomplish this individually with the formula:

    =if(datedif(a2,b2,"D")>8,TRUE,FALSE) and then counting the number of TRUE values.

    My issue is that I want to be able to formulate this as an array instead of a bunch of individual formulas.
    Below is my attempt at that. My idea was to return an array with TRUE values for each set of consecutive dates that have more than 8 days between them and FALSE values where that is not the case. And then count the number of TRUE values. It did not work.

    {countif(if(datedif(a2:d2,b2:e2,"D")>8,TRUE,FALSE),TRUE)} CTRL+SHIFT+ENTER


    Is there a way to formulate an array formula that accomplishes my goal?

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Count instance of >8 days between consecutive dates in a list of dates

    Try

    =SUM(IF(B2:E2-A2:D2>8,1,0))

    Enter with Ctrl+Shift+Enter

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count instance of >8 days between consecutive dates in a list of dates

    One way...

    =SUMPRODUCT(--(B2:E2-A2:D2>8))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    12-07-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    26

    Re: Count instance of >8 days between consecutive dates in a list of dates

    Both formulas work if all cells in the range contain a date, but do not work if blank cells are included in the range.

    I am not going to know exactly how long the list of dates is going to be. The dates are pulled using a formula, so there may be a few blank cells at the end of the list (the cells appear blank, but the cell value is a formula that makes it blank).

    Both formulas give #VALUE errors when "blank" cells are included in the range. Is there any way to counter this?

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Count instance of >8 days between consecutive dates in a list of dates

    Try this ...

    =COUNT(1/(B2:E2-8>A2:D2))

    Ctrl+Shift+Enter.

    http://www.excelforum.com/showthread...=1#post4575440

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count instance of >8 days between consecutive dates in a list of dates

    Quote Originally Posted by Keats713 View Post
    there may be a few blank cells at the end of the list (the cells appear blank, but the cell value is a formula that makes it blank).
    Try this array formula**:

    =SUM(IF(IF(ISNUMBER(B2:J2),B2:J2)-IF(ISNUMBER(A2:I2),A2:I2)>8,1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Adjust for the correct end of range.

  7. #7
    Registered User
    Join Date
    12-07-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    26

    Re: Count instance of >8 days between consecutive dates in a list of dates

    Tony Valko,

    Sorry for the late response. That formula works perfectly.
    Thank you for the help!

+ 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. Replies: 17
    Last Post: 02-14-2024, 07:07 AM
  2. Late count based on number of days between two dates for a list of dates
    By Keats713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2017, 11:03 PM
  3. Late count based on number of days between two dates for a list of dates
    By Keats713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2017, 06:35 PM
  4. Late count based on number of days between two dates for a list of dates
    By Keats713 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2017, 02:19 PM
  5. [SOLVED] Vlookup dates between two dates and count the number of days
    By nishikanth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2013, 03:25 AM
  6. Replies: 3
    Last Post: 02-16-2012, 01:51 PM
  7. Replies: 7
    Last Post: 01-16-2009, 05: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