find and count days in blocks of consecutive dates

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.
0
jeanniet (3)
11/12/2004 3:33:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1628 Views

Similar Articles

[PageSpeed] 7

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

"jeanniet" <jeanniet@discussions.microsoft.com> wrote in message 
news:A4621A78-BC0E-42E3-9E37-9FC2E89DA41C@microsoft.com...
>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. 


0
JulieD1 (2295)
11/12/2004 4:50:29 AM
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

"JulieD" wrote:

> 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
> 
> "jeanniet" <jeanniet@discussions.microsoft.com> wrote in message 
> news:A4621A78-BC0E-42E3-9E37-9FC2E89DA41C@microsoft.com...
> >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. 
> 
> 
> 
0
jeanniet (3)
11/12/2004 5:26:02 AM
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


"jeanniet" <jeanniet@discussions.microsoft.com> wrote in message 
news:DAED3F3E-8D2C-442E-98B5-14710677DD08@microsoft.com...
>
> 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
>
> "JulieD" wrote:
>
>> 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
>>
>> "jeanniet" <jeanniet@discussions.microsoft.com> wrote in message
>> news:A4621A78-BC0E-42E3-9E37-9FC2E89DA41C@microsoft.com...
>> >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.
>>
>>
>> 


0
JulieD1 (2295)
11/12/2004 5:41:46 AM
Seems to make sense, I'll give it a try
Jeanne

"JulieD" wrote:

> 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
> 
> 
> "jeanniet" <jeanniet@discussions.microsoft.com> wrote in message 
> news:DAED3F3E-8D2C-442E-98B5-14710677DD08@microsoft.com...
> >
> > 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
> >
> > "JulieD" wrote:
> >
> >> 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
> >>
> >> "jeanniet" <jeanniet@discussions.microsoft.com> wrote in message
> >> news:A4621A78-BC0E-42E3-9E37-9FC2E89DA41C@microsoft.com...
> >> >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.
> >>
> >>
> >> 
> 
> 
> 
0
jeanniet (3)
11/12/2004 11:59:01 AM
Reply:

Similar Artilces:

I need to compare 3 numbers and find the one in the middle
I have three numbers in a single row and would like to identify the middle number enter that number in another cell. Example: 1st # 2nd # 3rd # result 628 678 720 678 655 625 700 655 748 720 725 725 is there a function in excel that can do this? Thanks, Eddie Try =LARGE(A1:C1,2) VBA Noo -- VBA Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=56811 fasteddie wrote.....

Find Duplicate names and delate
Dear experts, I have a small doubt could you clarify that??? That is I find duplicate name but I want to delete one name only, if I filter DUPLICATE….. both names are showing… 1. Select the range of data including the header. You need to have headers for these columns 2. From menu Data>Filter>Advanced Filter>Copy to another location 3. In 'copy to' specify the target cell and check 'Unique records only' 4. Click OK will give you the unique list -- Jacob "Find Duplicate names and delate" wrote: > Dear experts, > I have a small ...

Finding a Median
I'm trying to write a query that will return a median for various values taken from a previous query. I've seen some suggestions in my searching, but I haven't been able to get them working. They are also all from before 2003 and refer to Access 97 and 2000. Has any functionality been added to 2003 for this? Or is there a non- code-based way to do it? I've seen it suggested to write a code to open the query, sort it, find the total number of records, divide it in half, then seek out the middle record using that value. I'm still very green when it comes to code, though...

Find a Value the first Time It Occurs
I have a row of values that shows the total cumulative number of sales of items by month. Occasionally, there may be no sales in a month for an item so the cumulative value would stay the same for more than one month. I want to select a number in the row the first time it occurs and not select it if it repeats. What are you wanting to do with the info? To return position (column number) of number 1234 within row 2: =MATCH(1234,2:2,0) A formula that signals it's the first occurence: =COUNTIF($A2:A2,A2)=1 This could be used in a helper row, or as a conditional format f...

Find/Replace in RichEdit 2.0
I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my CRichEditDoc/View application so that I can use the ITextDocument interface and can do things such as suspend/resume the Redo buffer. Problem is, now the Find/Replace dialogs don't seem to do anything. If I revert back to RichEdit 1.0 they do! What's going on? BUMP! adriangibbons@yahoo.co.uk (Adrian Gibbons) wrote in message news:<34a2acd5.0404130713.123dbaab@posting.google.com>... > I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my > CRichEditDoc/View application...

Find (but not find)
My program takes a name from sheet3 goes to sheet1 to Find the name. If it cannot find name, how do you do an If/End to Exit Do while or find out if name has been founf? I have "On Error Resume Next" in program. Thanks again for all your help Gordon As ALWAYS, post your code for comments & suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Gordon" <gwelch1938@yahoo.com> wrote in message news:1184612089.486737.144020@n60g2000hse.googlegroups.com... > My program takes a name from sheet3 goes to sheet1 to Find the na...

Query in date range
Hello. Thanks for helping. I would like to create a query that will sum up expenses for various categories within a date range. I have created the query that sums the amounts, but I want it to use a high end of "Date()" or "Today()" and a low end date range that is the first day of the current month. Any idea if this can be done? Thanks, On Oct 31, 6:05 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Use This -- > Between Date()-Day(Date())+1 AND Date() > > Remember that Date() is as of midnight and will not return a record as...

Finding an event
Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button, a new window (in same workbook) opens with a new sheet. My problem is that excel does not seem to have any events for close of window if there are multiple windows in a workbook. Can someone help Peter Peter, That triggers the Workbook_WindowActivate event, you can use that. HTH Bob "Peter Ostermann" wrote in message news:i9m5v8$7bv$02$1@news.t-online.com... Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button...

Find value in a column and insert rows above
The set up looks like this: ColU ColV ColW ColX Y N N N Y N N N N Y N N N N Y N N N Y N N N Y N N N Y N N N Y Y Columns will always be U through X and will always be sorted in this order. I need to find the first Y in each column and insert 2 rows above that row. On the blank row above the first Y, I need to highlight in yellow and put title in the first cell, such as New, Old, Existing, Deleted. Any help would be greatly appreciated. Thanks for your time, Dee If desired, send your file to my address below. I will only look if: 1. You send a copy of this ...

cannot find database
I have an excel spreadsheet that is supposed to update a access db. Whenever I try to save the .xls I get an error stating cannot find db. Even when I open the db with access, I get the error and the db opens anyway?????? This only happens on 2 out of 20 pc's and I cannot figure out why???????? Thanks ...

Sum if Condition is Equal in Range Date and find column
I want to make a sum if Range is a week number and if style is Equal to CONC-92 or CONC-45 Week# 49 Week# 50 CONC-92= 27 CONC-92= 30 CONC-45= 27 CONC-45= 30 Datas are in a pivot table and... Pivot table looks like this: Date CONC-92 CONC-45 CONC-92 CONC-45 12/7 5 5 10 10 12/8 2 2 10 10 12/9 5 5 10 10 12/10 5 5 10 10 please help -- Lorenzo Díaz Cad Technician ...

Trapping a NO FIND after a find
I use the code below to store a row number to a variable after a find. I would like to trap a NO FIND if the find is unsuccessfull Any ideas. FSt1 provided the code below Sub macfindrow() dim rn as string dim rng as range dim therow as long rn = inputbox("enter something to find") if rn <> "" then Set rng = nothing Set rng = range("A1:IV65536").Find(what:=rn, _ After:=Range("A1"), _ Lookin:=xlformulas, _ Lookat:=xlpart, _ ...

How do I find the out of office reply?
My out of office reply is missing from tools. How can I retrieve it? Out of Office reply <Out of Office reply@discussions.microsoft.com> wrote: > My out of office reply is missing from tools. How can I retrieve it? Unless you are using an Exchange server, you will not have the Out of Office Assistant. See this: http://www.slipstick.com/rules/autoreply.htm -- Brian Tillman ...

Multiple date parameters
I have three queries: MonthlyAll, MonthlyShows, and MonthlySpots. My main form is built off of MonthlyAll. It's main purpose is to provide a list of cities in a single form view, so that I can scroll through the cities and view the corresponding data. The main form contains two subforms: MonthlyShows and MonthlySpots. The subforms are linked to the main form by city and display the respective shows and spots by date. My question seems simple enough: Upon opening the form, I would like to type in a single date range ("Between [] And []"); and view only those shows and ...

List box (and queries) not sorting dates or showing correct headers
G'day all, I have inherited a system with a search function screen that is acting weird. There are actually two search screens, both constructed in a similar fashion, but the first does what it is supposed to do and the second is misbehaving. The form has a bunch of unbound controls allowing the user to enter search criteria. The only mandatory criteria is the sort order, which is set from a combo box. The data source for the combo box is a table, sys_GAPSSortBy, which contains a list of query names and a user- friendly label for use by the combo box. When the user clicks the "Searc...

How do I get the sentence count in a word document?
-- Many thanks in advance best regards Ian Word Count provides a count of words, characters, lines, and paragraphs but not sentences. Although a macro can be written to get a rough count, Word can't necessarily distinguish between the period at the end of a sentence and the period following an abbreviation. There must be some algorithm, however, as the "readability statistics" are based to some extent on the number of words in the average sentence, I think. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word...

Finding numbers in rows and highlighting them
Hi everybody :) I need help. This is a lotto system. I have a spreadsheet with 508 rows and 45 columns. In each row there are 45 numbers mixed (1 to 45). I need a script or some other way to find where particular (drawn) numbers are in each of the 508 rows. If my explanation is not clear please ask for farther clarification. Thank you in advance for your help. Joh -- Johncobb4 ----------------------------------------------------------------------- Johncobb45's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1658 View this thread: http://www.excelforum.com/showt...

How do I find data from a list (or table) and insert it in a row?
I used to use a spreadsheet program (2 years ago) that allowed me to lookup an item and insert it into the worksheet. I know the program was at least a year old at that time (2002). How do I create a list, reference it , and then insert an item into a row of my choice? Example: Make of item, item description, and cost. Does the data go on one worksheet and then get referenced and inserted into the list of another worksheet? If so, how? I can't seem to get any answers by reading books on the subject. I no longer have access to the old workbook from which to study. Maybe that ...

Advanced Find not finding all
"Advanced Find" in Outlook 2007 is not finding all email messages which match the criteria. It finds between 30 and 200 and then stops. I then have to work with those (e.g., delete them) and then click "Find Now" again to find another batch, dozens of times! What is wrong? How do I fix this and find all messages that match the criteria? My typical application is to split the entire contents of my .pst files by date. Do do this I make a copy of the file. In one I delete all messages older than the cutoff date (e.g., 1/1/2007), and in the other I delete all messages yo...

See the block email !
Hello ! I have some blocked email that wait a smtp connection. Is it possible to show them ? From's field, To's field and the body's field ??? Thanks for helping me .. If using Exchange 5.5 open files in IMCDATA\OUT with notepad. "news.microsoft.com" wrote: > Hello ! > I have some blocked email that wait a smtp connection. Is it possible to > show them ? From's field, To's field and the body's field ??? > > Thanks for helping me .. ...

How to autofill cell with today's date on edit
Hi, Tring to setup a cell to update with today's date when every I edit any of the other cells on that row. Anybody know an easy way to do this? Thanks See this: http://www.mcgimpsey.com/excel/timestamp.html -- Biff Microsoft Excel MVP "mjg916" <mjg916@discussions.microsoft.com> wrote in message news:A3FFB1D4-50E2-4856-9C03-51D024CEEA74@microsoft.com... > Hi, > > Tring to setup a cell to update with today's date when every I edit any of > the other cells on that row. Anybody know an easy way to do this? > > Thanks ...

How to: find reason for rejected messages?
I'm trying to find the reason, why certain messages get rejected by our exchange 2000 server. Is there a way to find out? Scenario: pullmail gets mails from pop3 mailboxes from, let's say "internetmailserver.com" and puts it in our exchange server "intranet.com". For most mails it works marvellous but for some mails it just fails and I can only see (output of pullmail command): .... 1 messages waiting << +OK 8488322 octets Message from: some.user@validdomain.com to: user@intranet.com SMTP message rejected, reply OK Unexpected response rsion:...

formula to count instances of something
Using Excel 2007. I want to create a series of cells with a formula that will count the number of cells elsewhere that contain a certain word. "[ ]" means a cell. Example: [Category 1] [sub category 2] [#of instances] [sub category 3] [#of instances] Elsewhere on that XL spread sheet, I would have rows that have [sub category 2] [data...] [data...] [sub category 2] [data..] [data..] [sub category 2] [data....] [data....] [sub category 3] [data] [data] [sub category 3] [data] [data] [sub category 3] [data] [data] [sub category ...

Sum If and Count If
Is there a way to use sumif and count if if your criteria is dates? In my spredsheet column A are dates. My spreadsheet looks like this. A B C D Date Loc. Stops $ 11/17/09 Miami 2 $1150 11/18/09 Orlando 1 $1000 I have tried =SUMIF(A3:A5,11/17/9,D3:D51). I have also tried converting column A to show 2009 instead of 09, but I still get 0 as the result for both. Hi TB, Try: =SUMIF(A3:A5;Date(2009;11;17);D3:D5) HTH, Wouter Sorry, ...

Can "Totals" be displayed in Advance Find results?
This seems like a question that would've been asked many times before, but I can't find any reference to is here. Can total search results be displayed in the search results pane? The only reference to a solution for this is by a company in Romania called Creative Software Solution. They have a screen grab at http://www.creativesolutions.ro/img/Customizations-GridCount.jpg. Anyone know how I can make the customization myself? Thanks! Andy. Dear Andy, There are few options. 1. Writing Reports and show total 2. Add Total as custom value and use javascript to add that va...