Consecutive Days

[DEllis]

In excel if I have a list of days say...
10/1/2010
10/2/2010
10/4/2010
10/5/2010
10/6/2010
10/7/2010
10/8/2010
10/9/2010
10/10/2010
10/11/2010
10/14/2010
10/15/2010
10/20/2010

And I want to know how many 7 days in a row there are say from 10/20/2010 up, by looking I can see that starting on 10/15/2010 there are 7 or more days consecutively. Is there a way to write this so that access will tell me (build a query or vba)

Thank you very much for any insight.

 

[revver]

Why do you want Acess to do this when the data are already in Excel?

Also, am I missing part of your question? It seems that 10/12/2010 and 10/13/2010 are missing from the list so that the seven day sequence doesn't start until 10/11/2010.

In any case, you could add another column to excel and use it to show the number of days the date in the current row is after the row above it like this.

10/1/2010....-
10/2/2010....[COLOR="Red"]1[/COLOR]
10/4/2010....[COLOR="Gray"]2[/COLOR]
10/5/2010....[COLOR="red"]1[/COLOR]
10/6/2010....[COLOR="red"]1[/COLOR]
10/7/2010....[COLOR="red"]1[/COLOR]
10/8/2010....[COLOR="red"]1[/COLOR]
10/9/2010....[COLOR="red"]1[/COLOR]
10/10/2010...[COLOR="red"]1[/COLOR]
10/11/2010...[COLOR="red"]1[/COLOR]
10/14/2010...[COLOR="gray"]3[/COLOR]
10/15/2010...[COLOR="red"]1[/COLOR]
10/20/2010...[COLOR="gray"]5[/COLOR]

You could then use conditional formatting to make the sequences easier to spot. For example you could change the background colour of every cell that has a value of 1.

If you really want to be told, you could use vba to start at the bottom of the column and work its way up, checking as it goes, how long the present sequence is. When the count has reached 7 at least and then drops back to 1, it could highlight the qualifying cells in some appropriate way.

 

[DEllis]

The data will be in access I just made a small excel sheet. The issue is that there will be dates that are missing as an employee doesn't work and then there will be times the employee works seven or more straight days. I need to figure out how to write the VBA so that it pulls the employees that work 7 consecutive days that is why the data had dates missing because the employee didn't work consecutive days during that week.

I am assuming I would start from date now and work my way backwards counting seven consecutive days. Does this make sense?

 

[revver]

I don't know your database structure but here are the broad steps I would have the vba routine use.

Dimension these variables 
 db, rs, iSeqCountNow, iSeqCountPrev, dtThisRec, dtPrevRec etc
Create a Recordset (include a field blnThisRecQualifies=F) ordered by date descending.
iSeqCountNow=1: iSeqCountPrev=1
MoveFirst and store date in dtThisRec
 MoveNext, assign dtPrevRec=dtThisRec : store date in dtThisRec
 Compare dates. if match, increment both iSeqCounts else iSeqCountNow=1
 If iSeqCountNow=1 AND iSeqCountPrev>=7 then 'the run is over
 Call SetBlnsToTrue
 ElseIf iSeqCountNow=1 'the run failed to reach 7
 iSeqCountPrev=1
 Else 'the run isn't yet over
 End If
 Repeat (the MoveNext)
Display results

There may be other steps that I haven't thought of but they will become obvious as you move into the detailed coding. Also the logic may need to be refined appropriately.

 

[DEllis]

Thank you, this looks like it will work. I will put it into action and see what I get.