Count number of weeks in which a given employee worked

Hi,

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.

Any ideas?

Thanks in advance.
2017-11-14-Sample-3--00324866xBDC2C.XLSX
twertsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
Hi Twerts,

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?


Thanks,

Alan.
twertsAuthor Commented:
Thanks, Alan.

The cut off-date will ultimately vary by technician. I can go in and replace those out later. I just put 2/1/2011 in as a dummy date that would work for testing purposes

I apologize for the ambiguity on the week. Ideally, the week would always end on Friday. That is, Saturday would be the first day and then Friday the last day of the week.

Thanks again.

-T
AlanConsultantCommented:
Hi Twerts,

Please could you add to your sample data the output that you want to get (just type the actual output into the cell).

That way we know what we are aiming for, and if we can't work out how you got an answer, we can clarify.

Maybe pick, say, half a dozen examples, and colour the cells yellow or something where you have put them.

Thanks,

Alan.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Download eBook
twertsAuthor Commented:
Thanks, Alan.

The attached file "Sample 4" has an example of what I am looking for.

I added a Sheet1 which has the list of the unique IDs.  I used a pivot table to pull this out and then just pasted the values into the cells.

I then filtered the data down to a single ID.

I added a column F to show how I would count the number of work days.

Next, I color coded the first unique date in a given set of weeks, each of which I would count as 1 day worked.  The first week coded as yellow.

Because the yellow week has time before the cut-off date, I want to ignore it. For my illustration, I return a result of 0 days worked that week to recognize that this week will be ignored.

The orange week has five unique dates highlighted, so it returns a result of 5 in Column F.

The green week has six unique dates highlighted, so it returns a result of 6 in Column F.

The blue week has seven unique dates highlighted, so it returns a result of 7 in Column F.

The dark green week at the bottom only has two unique dates highlighted, so it returns a result of 2 in Column F,

I then run a Countif function in cell F22845 that counts the number of instances where there were five or more work days in a week.  There are 3.

Sheet 1 then reflects "3" next to the given ID number.

I hope that helps.

Thanks again.
2017-11-14-Sample-4.xlsx
AlanConsultantCommented:
Hi Twerts,

That's great.

Two queres:

1)  In Sample4, cell Data!F167 has a value of zero - I was expected that to be 2.

Please can you explain why it is zero?

That will also impact on the count, which is currently three, but if Data!F167 were 2, then the total count would be four.


2) Is the Sample4 workbook how you want to get the actual results back - figures in Data!F:F and a separate sheet with a count result?




Thanks,

Alan.
twertsAuthor Commented:
Sorry.  The most recent version did not get uploaded.  The contents of the count cell should have been two.  But the count at the bottom should still be 3 since it should only count if the result is greater than or equal to 5.

The separate sheet is the preferred output.

Thanks again.
2017-11-14-Sample-4.xlsx
AlanConsultantCommented:
Okay - Got it!

Thanks,

Alan.
Gustav BrockCIOCommented:
You can use two subqueries.
First filter dates and find a week ID - the trick is here, that 2000-01-01 is a Saturday.
Then find weeks with five or more days per person.
Finally, count the number of those weeks per person.

data is your linked spreadsheet:

SELECT 
    T2.ID, 
    Count(T2.WeekId) AS WeeksOfFivePlus
FROM 
    (SELECT 
        T1.ID, 
        DateDiff("w",#1/1/2000#,[Date]) AS WeekId, 
        Count(T1.Date) AS AntalOfDate
    FROM 
        (SELECT 
            ID, 
            data.Date
        FROM 
            data
        WHERE 
            data.Date >= [Cut-off date]
        GROUP BY 
            ID, 
            data.Date)  AS T1
    GROUP BY 
        T1.ID, 
        DateDiff("w",#1/1/2000#,[Date])
    HAVING 
        Count(T1.Date) >=5)  AS T2
GROUP BY 
    T2.ID;

Open in new window

Result:

Result
/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
twertsAuthor Commented:
Thanks, Gustav.  This code and output looks like it will do exactly what we need.

I am getting a weird issue though. When I went to put your code into a new VBA module, it didn't seem to save. When I tried to close the program to restart (a tried and true solution for me to unknown errors), I received a compile error. I have pasted it in with the VBA editor screen so that you could see what I had.

Screenshot1.png
Do you know why this would throw an error?

Thanks.
Gustav BrockCIOCommented:
Yes. It is SQL, not VBA.

Open a new query and switch to SQL view and paste.

/gustav
twertsAuthor Commented:
Thanks, Gustav. And I must apologize. I barely know how to run a VBA macro and I have never used Excel's SQL features before.

Can you tell me how to open a query and switch to SQL view (or point me to a tutorial)?  I have tried to a Google an explanation but I have not been able to get it to work.

Thank you.
Gustav BrockCIOCommented:
It's in Access, not Excel. Both are tagged.

/gustav
twertsAuthor Commented:
That would explain it.  I was able to get it into Access and run the Query. This seems to be working!

Thank you.  Let me run some tests to make sure but this looks to be awesome.
twertsAuthor Commented:
Thank you both.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.