ej portfolio - Highway Scheduling Case Study

Transport Highway Administration

Designing a Shift Planning System to Replace Excel Workflows

＃B2B＃SaaS ＃GovernmentTech

Overview

The client

The Highway Transport Administration is responsible for managing the nation’s highway infrastructure. Focusing on ongoing maintenance and repair projects across Taiwan’s extensive road network. Each project involves about 50 workers from different companies, with shifts managed by government-appointed coordinators.

I led the end-to-end design of a shift planning system, transforming Excel-based scheduling into a structured digital workflow. My work included mapping coordinator pain points, translating Excel patterns into system logic, and validating flows with users. Key features: automated compliance checks, versioned drafts, and a familiar table-based interface, enabled fast adoption among coordinators aged 50+.

Duration

4 months

Collaborators

1 Product Manager, 1 Developers

Role

Product Designer

Contributions

UX/UI Design
User Interviews
Prototyping
Stakeholder Alignment
Engineer Collaboration

Problem

On-site coordinators relied on Excel to manage complex schedules, which carried risks of legal penalties, payroll disputes, and safety issues.

While Excel was familiar, it wasn’t built for compliance checks, version history, or coordinating across companies. For example, if two people saved different versions of the file, it was impossible to know which one was final. The agency needed a digital scheduling system that kept the ease of Excel but enforced labor law compliance and reduced risks.

What the government aimed to achieve

The government recognized these challenges and saw an opportunity to improve scheduling. Their goals were clear

→ Improve coordination efficiency
→ Ensure compliance with labor laws and avoid penalties
→ Enable fast adoption with minimal disruption
→ Deliver within a fixed timeline and budget

Research

My research relied on ongoing chat-based conversations with an on-site coordinator (aged 50+).

As our primary user and domain expert, the coordinator helped us clear doubts, explain how shifts were scheduled across multiple contractors, and validate our understanding step by step.

Research methods used throughout the project (Step-by-Step)

Conversations to clarify pain points

Ongoing chat-based discussions with the coordinator helped me clear doubts, understand edge cases, and learn details of the scheduling workflow as I was breaking it down.

Excel file reviews to map the workflow

I asked the coordinator to share real scheduling Excel files. Reviewing the files helped me see how shifts were tracked, how compliance was checked manually, and how versions were improvised, which revealed key gaps in the process.

Flow confirmation to validate understanding

After collecting initial information, I replayed each step back to the coordinator. This confirmed my mapping of tasks, risks, and dependencies, and opened discussion for new possible flows.

Walkthroughs with prototypes

Once designs were created, I ran interactive sessions with the coordinator. These walkthroughs allowed us to test early flows and refine them until they felt trustworthy and usable.

Insights

Research showed that coordinators stuck with Excel to avoid the effort of learning a new system. They wanted something just as familiar, but with built-in checks, records, and error prevention.

Excel felt “good enough” compared to adopting a new system

“I have been using Excel for a long time. It is not perfect, but I’d rather stick with it than figure out something new.”

Coordinators stuck with Excel because it was already part of their workday. They knew where to click, how to color cells, and didn’t have to spend extra time learning a new system.

Learning something new was the real barrier

“If it is something new with computer, I will probably mess it up.”

They felt that if a new system was introduced, even small errors would be harder to fix and more stressful.

Excel Analysis

I analyzed the Excel files to see how coordinators tracked shifts, checked compliance, and managed versions.

The Excel files followed a consistent structure: rows listed workers and their companies, while columns represented dates across weeks. Each cell was filled with color-based tags or manual codes to indicate shift status. Coordinators relied on horizontal scanning to check each worker’s hours against labor law limits, and vertical scanning to ensure daily site coverage. Names were entered manually, labor law formulas were created by hand, and version history was managed only by changing file names.

Excel analysis revealed the workarounds coordinators relied on, and those patterns directly shaped the system design into features like shift types, compliance checks, and version history.

Through the analysis of Excel files, we uncovered the scheduling behaviors coordinators relied on. Like using colors as status indicators, scanning rows and columns for validation, and renaming files for version control. These patterns directly shaped our system design, turning manual habits into built-in features such as standardized shift types, daily rollups, dropdown validations, automated compliance checks, and cloud-based version history.

Wireframe

Use mid-fidelity wireframes to validate flows with users. Focusing on schedule editing, automated labor law checks, and version control.

Step 1: User creates project → System auto-generates schedule

The system detects upcoming dates and builds a ready-to-edit grid matching the Excel layout coordinators were used to.

Step 2: User edits shifts → System applies changes (no version yet)

As shifts are edited (dropdowns / CSV / restore a prior draft to edit), the system reflects changes instantly in the grid, but does not create a version until the draft is saved.

Step 3: User saves draft → System validates compliance

When a draft is saved, the system runs compliance checks and records a version in history, ensuring past iterations can always be revisited.

Step 4: User submits final → System locks the schedule

When blocking issues are cleared, the system enables submission. Submission creates a final version and locks the schedule (admin override only).

Design Decisions

Choosing the right interaction pattern

We tested two approaches for assigning shifts through demo videos. Based on coordinator feedback, we chose (B) for its familiarity, clarity, and speed when editing multiple shifts.

(A) Click-to-edit on table cell

Select value from a separate legend.

(B) Dropdown in sidebar panel

Click cell, choose directly from dropdown.

Preserving Excel Habits to Make the System Familiar

Shifts could be uploaded through CSV files, edited in a table-like interface with the same rows and columns they were used to, and assigned using simple dropdown. By mirroring familiar patterns, coordinators could adopt the new system quickly without the steep learning curve.

Before

After

Tracking and Managing Schedule Versions

Every time a draft is saved, the system logs who saved it and when. Coordinators can revisit or restore past versions without juggling multiple files

I introduced three schedule views. Upcoming shows drafts still in progress, Active lists schedules that are currently running, and Past contains completed schedules that are locked as read-only.

Final Design

High-fidelity designs to refine components

A step-by-step journey of saving and submitting schedules

This flow shows how coordinators exit, save drafts, fix issues, or submit a final schedule while the system enforces labor law checks. Each state is designed to guide the user clearly: button states indicate when actions are enabled or disabled, pop-ups provide timely alerts and confirmations, and primary versus secondary actions highlight the correct next step for the coordinator.

Outcome

100% system adoption in 3 months

Even with Excel available, coordinators relied solely on the new system.

Reduced Compliance Risk

Automated validation ensured schedules met labor law requirements, preventing errors that previously exposed the agency to penalties.

30% Faster Shift Approvals

Streamlined workflows and built-in versioning cut down approval time significantly compared to manual Excel processes.

Reflection

This project taught us that digital transformation in government is built on trust. Users weren’t asking for new tools, instead they needed confidence that change wouldn’t disrupt their work.

By translating manual Excel processes into component-based interfaces (tables, status tags, editable fields), we laid the groundwork for scalable reuse across future projects. This points toward a replicable system architecture for AI-assisted design and rapid configuration.

We also redefined how research can work under constraints. With only one person to interview, we relied on ongoing validation to stay aligned with users cautious about digital change. This iterative approach proved effective for public-sector contexts and offers a repeatable model for future.