Scheduling 30-60 task annual projects with just 200 whole working days per year

Dell55

I want to develop a template sheet that I can use to easily schedule out 30 to 60 different 30 min subtasks evenly over 200 specific available work days each year. I started out trying to set up a gantt view row with "200d" working day duration then removing sat/sun, and inputting 62 other Non-working days in gantt view project setting in order to show the time line of actual working days available in the year but it doesn't seem to want to register the non working days so the time line is just 200d long ... so that's not working.

I was thinking I could divide the 200 days allotted for the project by the number of sub-tasks and then make that the duration of my sub tasks and set that as my base line and then set all my task rows up as finish-to-start predecessors. this way I could tell where I'm at by how my actual dates for sub-task completion compare with the baseline projections.

ether way i think i would need that "non-working" days project setting to work.

if there's an easier way... maybe I just pre fill a template with 30-60 rows with projected dates and count them down... id be very interested if anyone has tackled similar issues

Dell55

The goal would be to reliably track progress on different student therapy service contract frequencies (i.e 30 therapy sessions yearly or 60 session yearly) relative to the specific school calendar.

Genevieve P.

Hi @Dell55

I would personally use your idea of sub-tasks, with F-S predecessors, and one parent row showing the total 200d duration.

Even though task line will go over top of non-working days, it will not include those days into the Duration calculation and will highlight those dates in a slightly different colour in the Gantt chart.

Then what I would do is have the task line (the 1d task line for each sub-task of 30 minutes) change colour or cross out once it has been passed. You could have countdowns / summaries in the Sheet Summary section:

Cheers,

Genevieve

Dell55

yeah thank you @Genevieve P.!

This seems like a workable and valuable addition to current system I have in place. I used 0 duration for the visits and just added lag time between visits to space them out for the correct frequency. I also broke the year down into quarters as there are quarterly as well as yearly tasks (like a separate summer school contract) that id like to track for scheduling and completion.

here's how my cool turned out:

the idea is to make this a template that adjusts all the projected visit schedule dates to fit the 200 working day year based on the date that the year contract begins and use a separate one it for each student on my caseload

I'm guessing you used conditional formatting to strike dates in the past? I like the idea of using sheet summary to count down and track the dates.

Currently, I have single large Smartsheet with all the dates and data for the whole caseload formatted into one row per therapy visit. My hope is to reference data from the actual completion of the tasks(therapy visits) form that sheet and compared it to this timeline. Im thinking a check box or date completed column that compared with the "Projected visit schedule" column can return a list of at risk student names. I just need to figure out the most efficient way to compare the actual visits completed with each unique student timeline and hopefully set it up to reliably return a list of at student tasks/appointments in order of most to least at risk.

any ideas form the community ?

Genevieve P.

Hey @Dell55

Apologies for the delay! Yes, I used Conditional Formatting to strike out rows 🙂

If you have all the dates linked together with Predecessors, then you should be able to plug in a different Start Date in each of your new sheets and it will waterfall to show the correct schedule through the following rows.

In regards to comparing visits, you could use a checkbox column, yes. I would use a COUNTIFS function to see if the current student has a row in your source sheet with the corresponding date in this timeline sheet:

=IF(COUNTIFS({Student Name Column}, "Specific Name", {Date of Session}, [Projected visit Schedule]@row) > 0, 1, 0)

Then in your sheet summary field you can count those checkboxes in this sheet and compare it to the total sessions:

=COUNTIF([Checkbox Column]:[Checkbox Column], 1)

Based on your description it sounds like there may be quite a bit more going on than just setting up one sheet for 200 tasks. At this point it may be helpful to have someone take a look at your set-up and provide some guidance and suggestions on your configuration based on the full picture. If your account has access to Pro Desk sessions, these are 30-minute screen share coaching sessions that may be beneficial for you as you set this up. Here's more information: Get One-on-One Coaching with Smartsheet Pro Desk

Cheers,

Genevieve