How to graph a range of dates when only given a start and finish

cadenalford

I have a sheet where I have individual tasks each with their own start and finish dates that can overlap. They each have a certain # of people assigned to the tasks per day. Essentially, it looks like start: 5/15/23 | end: 6/25/23 | #ofPPL/Day: 5 and for each day in between those dates there will be 5 people. The goal is to be able to graph the # of people per day across all tasks, but I having a hard time figuring this one out.

Paul Newcome

Yes and no. It will require some manual entry, but you can set it up to be relatively templated. First you would need to figure out the maximum number of days you will need to accommodate and then add a buffer on. So if you don't think a project could ever take more than 2 years (730 days), I would suggest using 800.

Create a text/number column and manually enter the numbers zero through whatever that max with buffer is. Create a sheet summary field with a MIN function to pull in the earliest start date and another sheet summary field with a MAX function to pull in the latest end date.

Then in the sheet column that will house the dates you would use something like:

=IF([Start Date]# +[email protected]<= [End Date]#, [Start Date]# +[email protected])

Pro Tip: Use a separate column for your labels and a formula to only grab certain dates on a regular basis so that your horizontal axis doesn't get crazy cluttered trying to display every single individual day.

Paul Newcome

You would need to create a separate sheet where each date was on its own row. Then you could use a COUNTM/COLLECT combo to count how many people are working each day.

=COUNTM(COLLECT({Assigned To}, {Start Date}, @cell<= [Date Column]@row, {End Date}, @cell>= [Date Column]@row))

cadenalford

@Paul NewcomeIs there a way to autogenerate the column of dates? I have a start and end that is auto calculated for the entire project. Is there a column formula that will give me each date in a cell between those dates?

Paul Newcome

Yes and no. It will require some manual entry, but you can set it up to be relatively templated. First you would need to figure out the maximum number of days you will need to accommodate and then add a buffer on. So if you don't think a project could ever take more than 2 years (730 days), I would suggest using 800.

Create a text/number column and manually enter the numbers zero through whatever that max with buffer is. Create a sheet summary field with a MIN function to pull in the earliest start date and another sheet summary field with a MAX function to pull in the latest end date.

Then in the sheet column that will house the dates you would use something like:

=IF([Start Date]# +[email protected]<= [End Date]#, [Start Date]# +[email protected])

Pro Tip: Use a separate column for your labels and a formula to only grab certain dates on a regular basis so that your horizontal axis doesn't get crazy cluttered trying to display every single individual day.

cadenalford