find and count days in blocks of consecutive dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to find a way using Excel to find blocks of consecutive dates in a
list and then count how many days are in each block. Any help is appreciated.
 
Hi

if i'm understanding you correctly, you can use the COUNTIF function

=COUNTIF(range,">="&datevalue("lowest_date"))-COUNTIF(range,">"&datevalue("highest_date"))

e.g.
=COUNTIF(A1:A23,">="&DATEVALUE("2/10/04"))-COUNTIF(A1:A23,">"&DATEVALUE("4/10/04"))
counts all the days between 2/10/04 and the 4/10/04 inclusive

PS you might need to format the answer to general (format / cells / number
tab, general)

Hope this helps
JulieD
 
1/6/2000
1/13/2000
1/14/2000
1/15/2000
1/16/2000
1/17/2000
1/18/2000
1/19/2000
1/20/2000
1/21/2000
1/22/2000
1/23/2000
1/24/2000
1/25/2000
1/26/2000
1/27/2000
1/28/2000
1/29/2000
2/1/2000
2/2/2000
2/3/2000
2/4/2000
2/5/2000
2/6/2000
2/7/2000
2/8/2000
2/12/2000
2/13/2000
2/17/2000
2/18/2000
2/19/2000
Hi Julie,
I have lists of data with dates as above. What I need to do is find all the
runs of dates that are consecutive, such as 2/17,2/18, 2/19 and isolate them.
Then I need to count how many days are in each set of runs. I think I can
figure that out, but I first need an easy way of isolating the runs from the
entire list of dates. Thanks for the help.
Jeanne
 
Hi

not sure this is the most efficient and it would depend on how many dates
you have but you can use two helper columns to give you the information you
want
assuming the list below is in column A starting at row 1
in cell B1 type
=A1
in cell B2 type
=IF(A2-1=A1,IF(A2+1=A3,"",A2),A2)
and fill down your list of numbers (easiest way, is to move cursor over
bottom right of B2 until you see an + and then double click) - this will
give you the start & ending value of each run
in cell C1 type
=1
in Cell C2 type
=IF(B2="",C1+1,IF(B1="",C1+1,IF(A2=A1+1,C1+1,1)))
and fill down
this should give you the count of each run.

Hope this helps

Cheers
JulieD
 
Seems to make sense, I'll give it a try
Jeanne

JulieD said:
Hi

not sure this is the most efficient and it would depend on how many dates
you have but you can use two helper columns to give you the information you
want
assuming the list below is in column A starting at row 1
in cell B1 type
=A1
in cell B2 type
=IF(A2-1=A1,IF(A2+1=A3,"",A2),A2)
and fill down your list of numbers (easiest way, is to move cursor over
bottom right of B2 until you see an + and then double click) - this will
give you the start & ending value of each run
in cell C1 type
=1
in Cell C2 type
=IF(B2="",C1+1,IF(B1="",C1+1,IF(A2=A1+1,C1+1,1)))
and fill down
this should give you the count of each run.

Hope this helps

Cheers
JulieD
Click to expand...
 
Back
Top