Counting dates using "more than"

Hi all

This is hard to explain so please bare with me
I would like to count in a column with dates.

The formula should count or group the periods when the gap between th
dates are more than 3. (days)

1-May-04
4-May-04
5-May-04
6-May-04
7-May-04
11-May-04
14-May-04
15-May-04
19-May-04

In other words;
1,4,5,6,7 is one period
11,14,15 is another
and 19 is another

With the total being 3

Thanks in advance
Joey:confused

--
Message posted from http://www.ExcelForum.com

0
6/2/2004 12:52:56 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
346 Views

Similar Articles

[PageSpeed] 42

Assuming the dates are always in sequential order and the 
range of dates in this case is A1:A9:

=IF(SUMPRODUCT(--(A2:A10-A1:A9>3))>0,SUMPRODUCT(--(A2:A10-
A1:A9>3))+1,0)

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hi all
>
>This is hard to explain so please bare with me
>I would like to count in a column with dates.
>
>The formula should count or group the periods when the 
gap between the
>dates are more than 3. (days)
>
>1-May-04
>4-May-04
>5-May-04
>6-May-04
>7-May-04
>11-May-04
>14-May-04
>15-May-04
>19-May-04
>
>In other words;
>1,4,5,6,7 is one period
>11,14,15 is another
>and 19 is another
>
>With the total being 3
>
>Thanks in advance
>Joey:confused:
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
jason.morin (561)
6/2/2004 1:43:32 PM
Reply:

Similar Artilces:

having trouble adding more than one textbox to a form using vba
I am having problems with the following code. I can get 1 textbo created with the correct value, as long as only 1 checkbox is true. bu if they both are true, only one textbox is created and no value. can anyone see what I am doing wrong or help to improve code? Set wb = Workbooks.Open("PATH") If MultiPage1.Pages(0).Checkbox1.Value = True Then Set NewTextBox = MultiPage1.Pages(1).Add("forms.textbox.1") With NewTextBox .Width = 150 .Height = 18 End With Set rng wb.Worksheets("Sheet1").Columns(1).find(MultiPage1.Pages(0).Checkbox1.Caption) If Not rng Is Nothing...

Sending MORE than one workbook AND using .CC and .BCC as well?
Using XL'03, using Ron's tips, I have successfully sent one workbook, or sheet or range but I would like to send more than one workbook. I have used VBA to put the files in a folder and zip and send them, but I do not wish to zip them. How can I d this and how can I also use the e.g. .To and .CC .BCC as well. Many thanks. Simon If you are using outlook, just loop through all files adding each as a separate file, just as the zip is added, and also set the .cc and ,bcc properties as you to with the .To property. HTH Bob "Simon" <simonlavender@...

If the date is more than 6 weeks ago....
Can someone please tell me the expression for the following? I have 2 date fields [Date Issued] and [Response Received] Using conditional formatting in a report, i want [RESPONSE RECEIVED] to turn yellow if it's null and [Date Issued] was more than 6 weeks ago. All help much appreciated, as always! Kirstie [RESPONSE RECEIVED] Is Null And [Date Issued] < DateAdd("w",-6,Date()) PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com "Kirstie Adam" <kirstiea@ecosse.net(nospam)> wrote i...

Screen Flickering Problem when using CDialog::InvalidateData(FALSE) for More than 200,000 Times
Hi MFC experts, I wrote a folder CListView based encryption program with document and view support. This program will encrypt and decrypt files and folders and maintained the folder structures. I built a modaless CDialog object with CStaticCtrl and CProgressCtrl to display the files encryption and decryption process. I tested this application extensively. Then, I found a problem. In the application I call CDialog::InvalidateData(FALSE) to display the process progress. Every file will call InvalidateData(FALSE) 20 times. For file number below around 10,000, this works well; but for file numb...

Can I use Outlook 2003 on more than one computer?
I have three computers that currentlty have Outlook 2000 and I want to upgrade to 2003. Do I need to buy a separate software program for each computer? Or can I install the program on all three? you'll need 3 copies unless one is a laptop - then you may only need two (read the EULA to be sure). If you qualify for student and teachers edition (school age kids in grades K-12 or are a teacher) you can buy that version of office and install it on up to 3 computers. You can't use it for commercial purposes - like in a small business or home based business, but it's excellent fo...

How to use more than 7 IF functions in a formula?
I tried to post a question earlier but it never appeared on the messag list. Not sure if it went through. So here it is again. I am trying to set up a formula where when I type in a weight i another column it will automatically puts in an assigned value for tha weight. The problem is that the weights range from 110 through 260. have found that I cannot put in more than 7 IF functions in a formula. This is my first time trying to do something like this and I am havin problems. I know there has to be a way to do this. For example I tried this formula and it worked except I con only put ...

Autoclose Access Database if not using for more than a certain period
My Access database is working in a multi-user environment. Is there any function to close the access database if the user is not using the file for more than 2 hours? Thanks. See the following: http://support.microsoft.com/kb/210297/en-us or another option would be: http://support.microsoft.com/kb/304408 <xiaodan86@hotmail.com> wrote in message news:1178590698.082941.302910@e65g2000hsc.googlegroups.com... > My Access database is working in a multi-user environment. > Is there any function to close the access database if the user is not > using the file for more than 2 ho...

Can VLOOKUP be used to search for more than one possible value?
I am trying to create an academic progress form for college students that is specific to our department's requirements. We have different categories of classes that need to be completed: chemistry, biology, calculus, etc. Some of these are specific, e.g. Biology 171; for others, students have options, e.g. either Chemistry 151 or 161. We get a report on the courses students have completed each semester, which I put into a separate worksheet. I want the Excel form to automatically fill in course information in the appropriate category spaces based on course report data. I am attempt...

queries using the not command with more than one item
trying to do a query using not "example" or "example" can on ly get this to work with one item, not more than one need to say not abouit 8 items how do i do it? A couple of ways -- <>"Sam" AND <>"Joe" AND <>"Bill" AND <>"Jack" Not In ("RED","WHITE") -- Build a little, test a little. "access query question using not " "" wrote: > trying to do a query using not "example" or "example" > > can on ly ...

Need to filter more than one date
Excel 2003/XP I have an Excel sheet that has numerous rows and columns that I want to filter by 3 dates. I can use AutoFilter and filter one date but the others won't show up. I don't want to show the entire worksheet. Please advise. Thank you. Hi this can be achived using advanced filtering - check out http://www.contextures.com/xladvfilter01.html for details -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "HT" <asfafd@microsoft.com> wrote in message news:%23NMctcOSFHA.3144@tk2msftngp13.phx.gbl... > E...

Problem using more than 1 Excel version on same workbook?
I've got a multiboot system with a different Office in each OS. The main OS has Office 2003. And I created a workbook with lots of XIRR functions using Excel 2003. Recently, I've had a need to boot to the system that has Office XP. And go back and forth between the two OS. All of a sudden, I started getting a message about updating a Link when I opened the workbook under Office XP. So I booted to the OS that has Office 2003. Sure 'nuf I got the same message. The link was to ATPVBAEN.XLA, which seemed odd, as I've not seen that link before, and I do use XIRR a lot. I created a...

Lookup using more than one Lookup value
I am currently using vlookup to put together a summary sheet. However, I would like to Look up Column A and B, and then return the corresponding Column C figure. For example: John Brown 6 John Green 5 John Black 7 If Forename is Column A, Surname is Colume B and Column C is the value I need to return to the summary sheet. I'm not sure how to lookup more than one column? Any ideas?????? Charlou, =SUMPRODUCT(--(A1:A3="John"),--(B1:B3="Green"),C1:C3) Or if you want to have it on another sheet: =SUMPRODUCT(--(Sheet1!A1:A3="John"),--(Sheet1!B1:B3=&...

Can you use more than one publication design?
I'm using Publisher 2003 and I'm new to this app. I am very experienced with the other MS Office apps, but this has me stumped. I'm trying to create an "event book" for my church, using pictures of different activities throughout the year, and I want a different design for each ministry area (Children's Team, Youth, etc.). Every time I try to change the design, it applies the change to the entire document. For each new page, I've just been using the "Insert Duplicate Page" command. Is that my problem? Is there a way to create sections in Publ...

Big problems using more than one window
I=B4m having a hard time making excel function propperly=20 with more than one window open. I=B4m opening a new window in order to let users navigate=20 the application via a treeview control. BUT... if I didn=B4t save the workbook with two windows open=20 before I opened it, the new window opened is not active=20 meaning that buttons, treeview etc. cannot be activated: This Works: Open two windows save the workbook close excel, open=20 workbook again, now you can open and close windows without=20 loosing the ability to activate controls. This doesn=B4t work: Save workbook with one window op...

More than 9 recent files used
Does someone know how to increase the 'recently used files' to more tha 9? If that can't be done, is there another way to get the same thing? I would be nice to have a sub-menu in the File/Open menu that pops ou and lists the files. :confused ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

Countif to count more than one item?
All, Can Countif function count more than one criteria? I.e I have a column and want to count all but one of the criteria in that column. Column has MD, LK, Fac and No in it, I want to count all but No. Is this possible with the countif function? -- Adam ----------- Windows 98 + Office Pro 97 Hi Adam, > Can Countif function count more than one criteria? > I.e I have a column and > want to count all but one of the criteria in that column. > > Column has MD, LK, Fac and No in it, I want to count all but No. Lets say this is column A, then try this array formula. =SUM(IF...

Exchange 5.5 store.exe use more than 90%
i have a compaq proliant with nt 4 sp 6a and exchange 5.5 sp4 It used to work good but lately it uses more than 90% cpu for process store.exe i changed the anti virus software in january from network associates to trend but since that it worked good for about 2 months. anybody a idee what the problem is and what to do?? Bugs in store.exe can manifest themselves in this way. Apply latest post-SP4 Store hot-fix. If that does not help - call MS PSS and open support case (likely they will send you latest Store hot-fix, that is not yet released to general public yet). Tommy wrote: > i ha...

sumif using more than one criteria
I would like to know how to use the SUMIF function (or similar functio that would work) to sum a column of data according to 3 other columns. I have a column called "hours lost" one called "vendor" one called "problem type" and one called "date" basically the sum of hours lost for "vendor a" in month "january" i one cell, then "vendor a" in month "February" and so on down th column. then the sum of hours lost for "vendor b" for Month "january" then "vendor c" for month "januar...

Can OWA be used to access more than one mailbox at the same time?
Is it possible to access multiple mailboxes with a single log in. Like the way with Outlook it is possible to view multiple mailboxs at the same time. Not sure if it'd be the same time - but you can access another user's mailbox if you have permissions - using the complete URL to such a mailbox. For instance, server.domain.com/exchange/secondusername. You can't open both in the same OWA window like Outlook allows you to do by adding additional mailbox(es). -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ------------------------...

Is it possible to run more than one macro using one command button? if yes, how??
Hello, I had many macros that i want to run them all at once but not upon the opening of the excel workbook. i want to have one command button that runs them all. is it feasible?? Samer Gamal Hi Create another Macro that calls each other macro in turn. Sub RunAll() Call Macro1 Call Macro2 .. .. .. Call MacroX End Sub -- Regards Roger Govier <samergadelrab@gmail.com> wrote in message news:d57632d6-d468-4a71-b8f6-c9a87ce9150e@p25g2000hsf.googlegroups.com... > Hello, > I had many macros that i want to run them all at once but not upon the > opening of the excel workbook. i ...

Counting dates using "more than"
Hi all This is hard to explain so please bare with me I would like to count in a column with dates. The formula should count or group the periods when the gap between th dates are more than 3. (days) 1-May-04 4-May-04 5-May-04 6-May-04 7-May-04 11-May-04 14-May-04 15-May-04 19-May-04 In other words; 1,4,5,6,7 is one period 11,14,15 is another and 19 is another With the total being 3 Thanks in advance Joey:confused -- Message posted from http://www.ExcelForum.com Assuming the dates are always in sequential order and the range of dates in this case is A1:A9: =IF(SUMPRODUCT(--(A2:A10-A...

Add more than one series to a pivot chart using VB MS Access continued...
I'm trying to programmatically create a stacked bar pivot chart. Using "Programming Microsoft Office Access 2003" by Rick Dobson, I've created the chart. However, it doesn't distinguish between the different values for the series. Does anyone have any suggestions on how to create a chart using one column containing three values for the series? Essentially, this is a continuation of a previous post: http://groups.google.com/group/microsoft.public.access.formscoding/browse_thread/thread/e23f3506a6d561a0/674295d410a71cf9%23674295d410a71cf9" Any help is greatly appre...

how can I tell which license a RMS register used? and returning the same item more than once ?
I have 4 licenses and due to hardware problems and rebuilt machines I have lost track of which machines used which licenses. How can I determine that? Also how do I move a license? The software doesn't list any numbers to contact MS or even any suggestions on what to do it just keeps asking for a valid license key. I noticed today that it is possible to return the same item more than once. Is there any way to prevent that? Thank you ...

How do i sort contacts using more than one catagory ?
I am trying to create lists which have contacts sorted by more than one catagory. So including contacts that are linked to two or more specific catagories. Can this be done, if so how? thanks "samong" <samong@discussions.microsoft.com> wrote in message news:01E99022-AB21-487B-9365-2E66BB0C3F1B@microsoft.com... >I am trying to create lists which have contacts sorted by more than one > catagory. So including contacts that are linked to two or more specific > catagories. Can this be done, if so how? The Category field is non-sortable because it is a multi...

using lookup to copy more than one cell
I have a spreadsheet: > > Worksheet 2 > A B C D > 1 Date Item Price Total > 2 1st Apr Widget �1 �3 > 3 2nd Apr Brush �2 �2 > 4 3rd Apr Brush �2 �0 > 5 4th Apr Widget �1 �5 > > > I would like to create a formula in worksheet 1 that returns the data > held in columns A, B, C and D of worksheet 2 - but only where the > value in D is greater than 0. > > If the formula works, worksheet 1 should appear as: > > A B C D > 1 Date Item Price Total > 2 1st Apr Widget �1 ...