Rota analyser

Inspiration

Every four months, NHS junior doctors receive a large Excel spreadsheet — their rota. Figuring out which dates are eligible for annual leave and which shifts can be swapped requires manually cross-referencing complex trust rules: 6-week notice periods, night shift restrictions, colleague leave counts, and fatigue rules for swaps. It's slow, error-prone, and happens at the end of an already exhausting shift.

RotaClear uploads that same spreadsheet and does all the rule-checking instantly.

What it does

RotaClear is a three-screen web app:

1. Upload — the doctor uploads their Excel rota. No login, no account, no data stored.
2. Setup — they type their name into a searchable dropdown populated from the spreadsheet column headers, and choose whether they want to plan leave or find a swap.
3. Main view — two rule-powered tabs:
   • Annual Leave tab: A full color-coded calendar of the rota period. Pink = eligible for leave (date is 6+ weeks away, not a night shift, not a weekend, fewer than 6 colleagues already on leave). Gray = ineligible, with a tap-to-explain popup giving the specific reason. Sticky month sidebar for quick navigation.
   • Swaps tab: A calendar of the doctor's shifts, color-coded green (valid swap exists) or pink (no valid swap). Tapping a green shift shows every valid swap partner and their date. Validity is checked against three fatigue rules for both doctors: ≥11 hours between consecutive shifts, ≥48 hours after a night run, ≤7 consecutive shifts. Tapping a partner expands a pre-drafted email to the rota coordinator, ready to copy.

How we built it

React 18 + Vite + Tailwind CSS v4, fully client-side. @e965/xlsx parses the Excel file in the browser — no server, no data ever leaves the device. The rule engine is pure functions: calculateLeaveEligibility and calculateValidSwapsForDate. Swap checking pre-computes all doctor schedules once (buildPrecomputed) and runs fatigue validation in a single O(n) pass per candidate pair — fast enough to color-code an entire year's calendar on load. Session data is persisted to sessionStorage so the rota survives a page refresh but clears when the tab closes.

Challenges we ran into

The hardest part was swap validation. A naïve implementation rebuilds schedule data for every candidate pair, which caused Chrome to hang on a real 30-doctor rota. The fix was precomputing sorted shift arrays once and running fatigue checks on those arrays directly. Getting the calendar scroll behavior right — auto-scrolling to the current month on tab switch, accounting for sticky headers — was fiddlier than expected. And the swap date filter took several iterations: partner shifts must be strictly after the date being swapped, same-day conflicts need explicit Set lookups, and today must never be stale from a cached closure.

Accomplishments that we're proud of

A live, working tool that junior doctors can use on their actual NHS rota spreadsheet today. The rules are correctly encoded and the app handles a real 30-person, 4-month rota without performance issues. Everything is private by design.

What we learned

How to encode non-trivial rule systems as pure functions and compose them into a fast UI. The importance of separating precomputation from validation. How sessionStorage behaves differently from localStorage — and why that distinction matters for this use case.

What's next for Rota analyser

Multi-trust configuration (rules are currently hard-coded to one NHS trust), a mobile-optimised layout, and a leave balance display once the spreadsheet format exposes that data.

Built With

• javascript
• react
• sheetjs
• tailwindcss
• vercel
• vite