Automating Complex Payroll Logic for Detroit Detention Center Staffing

Project Overview

A staffing agency responsible for managing workforce shifts at the Detroit Detention Center was facing a logistical nightmare. While they used Homebase for time tracking and ADP for payroll, the two systems could not communicate the complex logic required by their government contract.

They needed a bridge solution to handle specific shift differentials without forcing their HR Director to manually calculate pay rates for 50+ employees every week.

The Challenge

The staffing agency struggled with unique contract rules that off-the-shelf software couldn't handle:

• Complex Shift Differentials: Pay rates changed based on the specific time of day (e.g., Night Shift premiums) and day of the week (Weekend premiums).
• Split Shifts: Employees frequently worked shifts that crossed pay periods (e.g., 10 PM to 6 AM). A single shift required two different pay rates, which Homebase could not calculate automatically.
• Manual Data Entry: The HR team had to export data, print physical rate charts, and manually calculate every employee's gross pay in Excel.
• High Error Risk: Manual calculations led to occasional payroll errors, requiring time-consuming corrections and eroding employee trust.

Our Approach

We moved the client from a manual "spreadsheet-based" workflow to a "one-click" automated application.

1. Workflow Analysis & Logic Mapping

• We audited their union/government contracts to map every possible pay scenario.
• defined specific rules for "Night Shift" (e.g., 11 PM - 7 AM) vs. "Standard Shift."
• Identified the exact column format required for a successful ADP import.

2. Custom Engine Development

• Built a calculation engine capable of "slicing" shifts. If an employee worked 8 PM to 4 AM, the engine automatically split the shift into "Standard Pay" (8 PM - 11 PM) and "Premium Pay" (11 PM - 4 AM).
• Automating the lookup of differential rates, removing the need for physical cheat sheets.

3. Desktop App Deployment

• Created a standalone desktop executable (EXE) using Python and Tkinter.
• Designed a "Drag-and-Drop" interface requiring zero technical skills.
• The user simply drags the raw Homebase CSV file onto the app icon.

4. Automated Reporting

• Manager Report: A clean PDF summary of total hours and overtime for approval.
• Payroll File: A formatted CSV file that matches ADP's strict import template, ready for upload.

Results

The impact was immediate and transformed the department's weekly routine:

• 99% Time Reduction: Payroll processing went from a 2-day stress event (10-12 hours) to a 30-second task.
• 100% Accuracy: Automated logic eliminated human error on split-shift calculations.
• Instant ROI: The development cost was recouped in less than one month of saved administrative labor.
• Scalability: The agency can now take on more government contracts without needing to hire additional payroll staff.

Key Takeaways

This project demonstrated that:

1. SaaS has limits: Platforms like Homebase are great for general use but often fail at niche, complex contract logic.
2. Custom > Manual: Building a small, bespoke tool is often cheaper and more effective than trying to force-fit enterprise software.
3. Accuracy builds trust: Eliminating payroll errors improved the relationship between the agency and its workforce.
4. Simplicity wins: The client didn't need a dashboard; they needed a button. The "Drag-and-Drop" interface ensured 100% adoption.

---

Struggling with manual data entry or complex payroll rules? Schedule a free consultation to see how custom automation can save you hours every week.