CODEWITHSUNDEEP
excelgraphchartsexcel-2016
DCanimate
DCanimate

Reputation: 11

Excel - Number of active projects by employee over time

Problem:

I'm trying to figure out how I can graph the number of active projects over the course of a year by employee in excel. I am semi-familiar with Pivot Tables and I wasn't sure if this was something I could accomplish with the data I have or if I have to work out some formula in order to get the data represented in a chart.

Sample Data:

+------------------+------------+-----------------+-----------------+-----------+
| Submission Date, | Proof Date | Production Time | Completion Date |  Artist   |
+------------------+------------+-----------------+-----------------+-----------+
| 10/13/2015       | 10/19/2015 |               6 | 1/11/2016       | Mandi     |
| 10/27/2015       | 11/2/2015  |               6 | 1/4/2016        | Laura     |
| 11/23/2015       | 1/12/2016  |              38 | 1/18/2016       | Christina |
| 11/24/2015       | 12/2/2015  |               8 | 1/5/2016        | Kim       |
| 11/24/2015       | 12/2/2015  |               8 | 1/5/2016        | Kim       |
| 11/30/2015       | 12/3/2015  |               5 | 1/15/2016       | Kim       |
| 11/30/2015       | 12/3/2015  |               5 | 1/15/2016       | Kim       |
+------------------+------------+-----------------+-----------------+-----------+

Desired Output:

The goal is to have a line graph that show the total number of active jobs, grouped by week or month, per an artist over the course of the year.

Upvotes: 0

Views: 1134

Answers (1)

elmer007
elmer007

Reputation: 1445

Assuming that an "active" project is one that has been "submitted" but not "completed", how about the following:

  1. Set up a list of your Artist names
  2. Set up a row of week-dates (I chose Sundays)
  3. Use COUNTIFS to count active projects
  4. Use the generated table of projects/artist/time to drive your graph

Using my sample setup shown below, with the first week-date in cell I1, your COUNTIFS would be:

=COUNTIFS($B:$B,"<"&I$1,$E:$E,">"&I$1,$F:$F,$H2)

This counts the number of jobs where the "Submission Date" is less than the current week-date and the "Completion Date" is greater than the current week-date (i.e., it's "active") and the "Artist" name is the current row's.

Screenshot to show setup:

enter image description here

Upvotes: 1

Related Questions