Ops Logistics Calendar & Task List

Purpose

This SOP provides step-by-step instructions for creating and maintaining the 6 - Ops Logistics and 6 - Ops Task List tabs in the DORIS Research project management workbook. These tabs serve as the operational backbone for tracking project timelines, task assignments, and weekly logistics across all active projects.

Responsible Party

Operations Manager

Interdependencies

These tabs rely on data from and feed data to the following worksheets:

Project Setup tab: Contains the master template of tasks and phases for new projects
Milestone Dates tab: Stores key milestone dates that drive task scheduling via VLOOKUP formulas
Holidays tab: Provides holiday exclusions for WORKDAY calculations

Directions

Part 1: Creating the 6 - Ops Task List Tab

2. Copy project tasks from Project Setup: For each new project, go to the Project Setup tab and copy all task rows for that project. Paste these rows into the 6 - Ops Task List tab, maintaining the Project, Phase, Milestone Lookup Field, Task, Assigned To, and days from milestone columns.

3. Set up Status column: Manually enter or select status for each task using one of these values: To Do | In Progress | Completed | Waiting - External

4. Configure the Week Number formula: In the Week Number column, enter the formula: =WEEKNUM(J2) where J2 is the Start Date cell in that row. Copy this formula down for all tasks.

5. Configure the Start Date formula: In the Start Date column, enter this formula: =WORKDAY(VLOOKUP($A2&$C2,'Milestone Dates'!$A$11:$G$993,5,FALSE),H2,Holidays!$B$2:$B$264) This formula looks up the milestone date from the Milestone Dates tab by concatenating Project name ($A2) and Milestone Lookup Field ($C2), adds the 'days from milestone' offset (H2), and accounts for holidays.

6. Configure the End Date formula: In the End Date column, enter: =WORKDAY($J2,$L2,Holidays!$B$2:$B$264) where J2 is the Start Date and L2 is the Duration. This calculates the end date based on working days, excluding holidays.

7. Enter Duration values: Manually enter the number of working days required for each task in the Duration column. Leave blank for single-day tasks or tasks without a duration requirement.

8. Mark milestones: In the Milestone column, enter 'Yes' for any task that represents a key milestone. This helps identify critical deliverables and decision points.

9. Maintain the list: Update Status as work progresses. Update Tentative / Confirmed as dates are confirmed with clients. Add notes in the Notes column for context, blockers, or special instructions.

Part 2: Creating the 6 - Ops Logistics Tab

10. Set up the calendar structure: Create a calendar grid with days of the week (M, T, W, Th, F, Sa, Su) across the top row. Below the day labels, enter actual dates for each day, creating columns for every day of the year. Group dates by month for easier navigation.

11. Create the project list: In column A, list all active projects. Include a Size column (column B) with project size categories: Up to 100, Up to 200, Up to 300, Up to 400.

12. Populate logistics data: For each project row, manually enter abbreviated task codes or milestone markers in the cells corresponding to the date when that activity occurs. Use abbreviations like 'W' for writing tasks, 'gate/M' for gate meetings, 'review' for review periods, 'PO' for purchase order tasks.

13. Cross-reference with the Task List: Review the 6 - Ops Task List to identify when key tasks and milestones fall within the calendar. Plot these on the Ops Logistics grid to visualize the entire operational workload across all projects.

14. Update regularly: As projects progress and dates shift, update both the Task List and the Logistics calendar to maintain accuracy. Use the Logistics tab during weekly planning to identify resource conflicts and workload distribution.

Resources

DORIS Research project management workbook (SOPs.xlsx)
Project Setup tab (master task template)
Milestone Dates tab (reference for VLOOKUP formulas)
Holidays tab (for WORKDAY calculations)