Count number of weeks in which a given employee worked
I have a spreadsheet (attached) with a list of Jobs with corresponding employee number as well as the date that the employee performed a particular job.
I need to know how many weeks that each employee performed work on five or more days in a given week. The number of jobs performed is not important for this analysis. Rather, we just need a list of ID numbers with a corresponding number of weeks where there were at least one job in five or more days that week.
Secondarily, there is a cut-off date (which will vary per employee) for which I need to ignore any jobs and/or weeks before that date.
I know how to use a pivot table to get a list of the unique ID numbers but the conditional count here is beyond my Excel abilities. Access may be able to do this easier but I am utterly without skill in that program.
1) The 'Cut-off date' is always 1 Feb 2011 is that correct?
2) What is your definition of 'a week'? For example, does it always start on Sunday, and end of Saturday? Or perhaps it means any two dates that are within 6 days of each other, so any rolling 7 day period, where the employee works at least five of those seven days?