Custom OCC-Inspired Scheduling System
Budget: $30 – $250 USD
OCC-Style Crew Scheduling & Manpower System
I’m sharing the full specification for the crew scheduling and manpower system we need built. This design mirrors an OCC (Operations Control Center) setup but is focused entirely on crew scheduling, compliance, onboarding, and manpower reporting. Below are the detailed requirements, organized by module:
Core Data & Settings
Settings sheet with tables for:
Airlines (International vs. Domestic, contact emails, flight frequency)
Roles (name, description, required certifications)
Certifications (validity, renewal rules)
Crew roster (name, ID, status: Active/New Hire/OJT/Temp, allowed roles, certifications)
Crew groups (Ramp Crew A, Ramp Crew B, Guest Services, etc.)
Shifts (AM/Mid/PM with times)
Email profiles (airline contacts, subject prefixes)
Data validation: dropdowns for roles, certifications, crew groups, shifts, airlines.
Error-proofing: dependent dropdowns so crew can only be assigned roles they’re qualified for.
Master Scheduling
Master_Schedule sheet as the single source of truth:
Columns: Date, Airline, Flight/Shift, Crew Name, Role, Certification, Crew Group, Shift, Status, Notes
Each row = one crew member per day per assignment
Role validation: flags if assigned role is not allowed for that crew member.
Crew group assignment: crew can appear in different groups on different days.
New Hire & OJT
Status column: Active, New Hire, OJT, Temp
New_Hire_Schedule sheet: temporary schedule view for new hires
Training_Log sheet: tracks OJT progress (crew, date, task, mentor, feedback, completion)
New_Hire_Cheat sheet: quick reference guide with role definitions, requirements, expectations, mentor assignments
Filtering & Reporting
Filter_View sheet:
Dropdowns for Date, Airline, Flight/Shift, Role, Crew Group, Status, Certification
Helper formula to mark matching rows
Manpower_Output sheet:
Clean, screenshot-ready manpower list
International airlines: manpower per flight
Domestic airlines: manpower per shift (AM/Mid/PM)
Shift Coverage Dashboard:
Shows crew counts per shift/role/airline
Highlights coverage gaps or overloads
Compliance & Irregular Ops
Compliance monitor:
Flags rest rule violations, max consecutive days, certification expiry
Incident/Irregular Ops Log:
Tracks disruptions (weather, diversions, staffing issues)
Columns: Date, Airline/Flight, Crew Group, Issue, Resolution
Airline Schedule Integration (Optional)
Airline_Schedule sheet:
Imported via Power Query when available
Cleaned and standardized for linkage
Linkage: formulas suggest manpower slices by flight/shift
Refresh: manual or on-open, with “Last Updated” timestamp
Communication & Email
Email-ready outputs:
International: manpower list per flight
Domestic: manpower list per shift
Contacts in Settings: airline → email(s), subject prefix
Optional VBA macro:
Captures manpower list as image/PDF
Auto-fills email recipients and subject
Logs “Sent” entry with date/airline/shift
Formatting & Safeguards
Consistent table design, screenshot-ready outputs
Conditional formatting for compliance flags, coverage gaps, new hire/OJT highlighting
Optional change log for audit trail
Formula-first approach (FILTER, XLOOKUP, INDEX/MATCH); VBA only for email automation
Explicitly no equipment tracking
Edge Cases to Support
Crew double-booking detection
Role eligibility changes mid-week
Certification expiry mid-schedule
International flight time changes
Last-minute PTO/sick calls
OJT progression auto-updates
Deliverables:
Workbook with all sheets/modules listed above
Validation and formulas for dropdowns, compliance, coverage
Conditional formatting standards
Optional VBA for email automation
Short “How to use” guide
Assumptions:
Time zone: PST
Mix of international (1–2 flights/day) and domestic (24/7) airlines
Supervisors need quick filters, screenshot-ready outputs, and simple sending
No equipment tracking
Next Steps:
Confirm lists (Airlines, Roles, Certifications, Crew Groups, Shifts, Email contacts)
Provide sample Master_Schedule data for testing
Decide on email automation scope
Set coverage targets per airline/shift
I’m sharing the full specification for the crew scheduling and manpower system we need built. This design mirrors an OCC (Operations Control Center) setup but is focused entirely on crew scheduling, compliance, onboarding, and manpower reporting. Below are the detailed requirements, organized by module:
Core Data & Settings
Settings sheet with tables for:
Airlines (International vs. Domestic, contact emails, flight frequency)
Roles (name, description, required certifications)
Certifications (validity, renewal rules)
Crew roster (name, ID, status: Active/New Hire/OJT/Temp, allowed roles, certifications)
Crew groups (Ramp Crew A, Ramp Crew B, Guest Services, etc.)
Shifts (AM/Mid/PM with times)
Email profiles (airline contacts, subject prefixes)
Data validation: dropdowns for roles, certifications, crew groups, shifts, airlines.
Error-proofing: dependent dropdowns so crew can only be assigned roles they’re qualified for.
Master Scheduling
Master_Schedule sheet as the single source of truth:
Columns: Date, Airline, Flight/Shift, Crew Name, Role, Certification, Crew Group, Shift, Status, Notes
Each row = one crew member per day per assignment
Role validation: flags if assigned role is not allowed for that crew member.
Crew group assignment: crew can appear in different groups on different days.
New Hire & OJT
Status column: Active, New Hire, OJT, Temp
New_Hire_Schedule sheet: temporary schedule view for new hires
Training_Log sheet: tracks OJT progress (crew, date, task, mentor, feedback, completion)
New_Hire_Cheat sheet: quick reference guide with role definitions, requirements, expectations, mentor assignments
Filtering & Reporting
Filter_View sheet:
Dropdowns for Date, Airline, Flight/Shift, Role, Crew Group, Status, Certification
Helper formula to mark matching rows
Manpower_Output sheet:
Clean, screenshot-ready manpower list
International airlines: manpower per flight
Domestic airlines: manpower per shift (AM/Mid/PM)
Shift Coverage Dashboard:
Shows crew counts per shift/role/airline
Highlights coverage gaps or overloads
Compliance & Irregular Ops
Compliance monitor:
Flags rest rule violations, max consecutive days, certification expiry
Incident/Irregular Ops Log:
Tracks disruptions (weather, diversions, staffing issues)
Columns: Date, Airline/Flight, Crew Group, Issue, Resolution
Airline Schedule Integration (Optional)
Airline_Schedule sheet:
Imported via Power Query when available
Cleaned and standardized for linkage
Linkage: formulas suggest manpower slices by flight/shift
Refresh: manual or on-open, with “Last Updated” timestamp
Communication & Email
Email-ready outputs:
International: manpower list per flight
Domestic: manpower list per shift
Contacts in Settings: airline → email(s), subject prefix
Optional VBA macro:
Captures manpower list as image/PDF
Auto-fills email recipients and subject
Logs “Sent” entry with date/airline/shift
Formatting & Safeguards
Consistent table design, screenshot-ready outputs
Conditional formatting for compliance flags, coverage gaps, new hire/OJT highlighting
Optional change log for audit trail
Formula-first approach (FILTER, XLOOKUP, INDEX/MATCH); VBA only for email automation
Explicitly no equipment tracking
Edge Cases to Support
Crew double-booking detection
Role eligibility changes mid-week
Certification expiry mid-schedule
International flight time changes
Last-minute PTO/sick calls
OJT progression auto-updates
Deliverables:
Workbook with all sheets/modules listed above
Validation and formulas for dropdowns, compliance, coverage
Conditional formatting standards
Optional VBA for email automation
Short “How to use” guide
Assumptions:
Time zone: PST
Mix of international (1–2 flights/day) and domestic (24/7) airlines
Supervisors need quick filters, screenshot-ready outputs, and simple sending
No equipment tracking
Next Steps:
Confirm lists (Airlines, Roles, Certifications, Crew Groups, Shifts, Email contacts)
Provide sample Master_Schedule data for testing
Decide on email automation scope
Set coverage targets per airline/shift