Graph order count per user (between start and end dates)

[qlander]

Hi All,

I have a series of project for several people that have start and end dates, and I wanted to create a graph to count the number of active projects over time (per day) for each owner - would have to be line graph series (each line representing each owner).

ie

Owner---Project----StartDate----EndDate
Dave----ProjectA----06/01/2018---05/02/2018
Adam---ProjectC----10/01/2018---15/03/2018
Sarah----ProjectD----04/01/2018---25/02/2018
Adam----ProjectE----13/01/2018---17/02/2018
Sarah----ProjectF----17/02/2018---22/04/2018
Sarah----ProjectE----22/01/2018---10/02/2018
Dave----ProjectG----01/02/2018---05/03/2018
...etc...

Y Axis = Number of Projects
X Axis = dates (every day)

 

[Panda514]

Hello, I should be able to help you with this, but I have a couple of questions. 1. Does your excel use dd/mm/yyyy format or is that your preference? I'm asking because my excel automatically uses mm/dd/yyyy/ so when I use the Today() formula it is in mm/dd/yyyy. 2. What date range are you using? Do you mean every day as in every day of the year? Or do you want to see the graph monthly?

 

[qlander]

Hello, I should be able to help you with this, but I have a couple of questions. 1. Does your excel use dd/mm/yyyy format or is that your preference? I'm asking because my excel automatically uses mm/dd/yyyy/ so when I use the Today() formula it is in mm/dd/yyyy. 2. What date range are you using? Do you mean every day as in every day of the year? Or do you want to see the graph monthly?

Hi,
1) the date format is set by location (mm/dd/yyyy is a usa/american format). Either way, excel caters for this (format cell -> date -> location/format type)
2) the start dates and end dates vary (some are 2 weeks apart, some are months apart), the start date will always be before the end date. The graph would be a representation of time over actual calender days (ie every day of the year, since the start and end dates are very specific) to generate the total count on any particular day/date/period for each owner.

something like this (as a line graph):

or can be another type of chart I guess for easier representation.

 

[Panda514]

I think I have figured out what you are looking for. If your data is set up with Owner in col a, project in col b, start date in col c and end date in col d, you could add a field called distinct list of names in col e. The formula to type into cell E2 is =IFERROR(INDEX($A$2:$A$8, MATCH(0, INDEX(COUNTIF($E$1:E1, $A$2:$A$8), 0, 0), 0)), "")

Once you have a list of distinct names, I would copy and paste as a transpose, so that there is a column for each name.

I made a column called Months in G1 then pasted the names in G2. In order to see if each monthly date for a person is between any of the dates in the table, paste this formula in cell H2=COUNTIFS(C:C,"<="&G2,D:D,">="&G2,A:A,$H$1) this formula in cell I2=COUNTIFS(C:C,"<="&G2,D:D,">="&G2,A:A,$I$1) and this formula in cell J2 =COUNTIFS(C:C,"<="&G2,D:D,">="&G2,A:A,$J$1)

Then highlight the table that has the months and names and insert a stacked line graph.

Here is a picture of my excel sheet:

https://www.pastepic.xyz/image/6kaph

I hope this helps! I used mm/dd/yyyy format for my dates