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

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.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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]# + Number@row<= [End Date]#, [Start Date]# + Number@row)

    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!