Excel Calendars & Schedules: Dynamic Monthly Calendar, Shift Rota & Gantt with Formulas
Build the schedules teams actually print and share: a monthly calendar that redraws from one date, weekends, closed days and today in colour, ISO weeks, working days and paydays, a 4-on-4-off rota from one pattern, hours and overtime, a daily Gantt with progress bars, a milestone status board and a yearly planner with a chart, in 8 hands-on labs.
The first lesson is free — no account needed.
About this course
An Excel calendar and schedule course that builds the planning sheets of one fictional distribution hub, Northgate, from formulas instead of hand-typed dates, for office staff, team leads, shift planners and anyone who maintains a schedule in Excel. You build a monthly calendar that redraws from one date with WEEKDAY and a SEQUENCE grid, colour weekends, closed days and the as-of date with formula rules, list each month's working days, ISO week and payday with NETWORKDAYS and WORKDAY, generate a 4-on-4-off rota for four crews from one pattern with MOD and INDEX, turn shift codes into hours and overtime, draw a working-day Gantt with progress bars, track milestones as overdue or due soon, and summarise a yearly planner with COUNTIFS and a stacked column chart. The first lab is free.
Lessons
- A Dynamic Monthly Calendar: First Monday with WEEKDAY & a SEQUENCE GridFree
- Highlighting Weekends, Closed Days & the As-of Date with Formula Rules Pro
- Week Numbers & Working Days: ISOWEEKNUM, NETWORKDAYS & WORKDAY with a Closed-Day List Pro
- A 4-on-4-off Shift Rota: Repeating Patterns with MOD & INDEX Pro
- Weekly Hours & Overtime from a Shift Rota: COUNTIF per Shift Code & MAX(0, …) Pro
- A Daily Gantt with Progress: WORKDAY End Dates & Two Formula Rules Pro
- Milestones: Days Remaining, Overdue / Due Soon Status & the Next Milestone Pro
- Yearly Planner Summary: Events per Month with COUNTIFS & a Stacked Column Chart Pro