Sleeper: Count weeks with broken/unbroken date ranges with vba

[stevembe]

I originally posted this on a different Excel forum but received no responses so am thinking this can not be achieved. I know very little VBA but would appreciate it if anybody can tell me if the following can be achieved with VBA:

In column J I have a number of dates, some consecutive, some with a date(s) missing in a week. I am trying to work out how to identify the dates that are all within the same week, week commencing Monday to Sunday and count each as 1 occurrence and for the counting to reset when there is a change in the detail of column A which is employee number.

For example if I have:

12 Oct 15
13 Oct 15
14 Oct 15
15 Oct 15
16 Oct 15
This counts as 1 Occurrence

12 Oct 15
13 Oct 15
14 Oct 15
Also would count as 1 occurrence

12 Oct 15
13 Oct 15
15 Oct 15
16 Oct 15
Also would count as 1 occurrence.

In column K I would also like to total these occurrences up for each employee.

Thanks for taking the time to read.

[SamT]

Hey Steve,

Please post a link here to the thread on the other Excel forum.

I think you're going to have to show us 30 or so rows in Columns A and J. The other columns should all be blank.

[stevembe]

Hi Sam, the post on the other forum is exactly the wording that is on here. Not sure what you mean by showing you 30 or so rows. Sorry, a bit confused?

[Aussiebear]

Post an attachment showing the layout of your data. Otherwise we are simply guessing as to what may be required. Besides its good manners to indicate where the other forum is so that those people here who are interested in this issue can see what help is currently being offered.

[stevembe]

Hi, the link to the other thread is here but no comments have been received so no help has been offered which is why I didn't share the link, apologies.

http://www.mrexcel.com/forum/excel-questions/893587-countif-date-continuous-broken-range-same-week.html

The data looks like this, column I is the date, column J counts continuous occurrences using
=--(I3<>WORKDAY(I2,1)) and column K adds occurrences for each employee based against a change in employee number that is in column F using
=IF(F2=F3,0,SUMIFS($J$2:$J$9739,$F$2:$F$9739,$F2))


21-Jan-15

0

0



22-Jan-15

0

0



23-Jan-15

1

0



07-Apr-15

0

0



08-Apr-15

0

0



09-Apr-15

0

0



10-Apr-15

1

2



14-Sep-15

0

0



15-Sep-15

1

1



10-Feb-15

1

0



15-Jul-15

0

0



16-Jul-15

1

2



11-Mar-15

0

0



12-Mar-15

0

0



13-Mar-15

1

1



07-Jan-15

0

0



08-Jan-15

0

0



09-Jan-15

0

0



12-Jan-15

0

0



13-Jan-15

1

0



06-Jul-15

0

0



07-Jul-15

1

0



08-Sep-15

0

0



09-Sep-15

0

0



10-Sep-15

0

0



11-Sep-15

1

3



18-May-15

1

1



20-Jan-15

0

0



21-Jan-15

1

1



06-Jul-15

0

0



07-Jul-15

1

1



07-Jan-15

0

0



08-Jan-15

1

0



25-Mar-15

0

0



26-Mar-15

0

0



27-Mar-15

0

0



30-Mar-15

0

0



31-Mar-15

0

0

[SamT]

How can we help you if you don't give us what we need?

How can the blokes on mrexcel.com help you if you don't give them what they need?

Don't expect anybody besides yourself to jump back and forth between forums.