Complicated scheduling & pay spreadsheet, willing to pay a premium $$$ Hello, I am looking to hire someone to build a fully automated Google Sheets system to track my work shifts, hours, compliance, pay rules, biweekly payroll, and long-term career totals. I am a bus operator in Ontario and I want this sheet to handle 10 years of tracking, enforce Hours-of-Service rules, automate pay calculations, and produce biweekly, monthly, yearly and career summaries with charts. Below is a full breakdown of the requirements. I’m providing extremely detailed rules so that you can build this sheet without needing to guess or redesign as you go. I need someone who completely understands the project and can communicate effectively if they have any questions. This will be useful for your review Hours of service | The Official Ministry of Transportation (MTO) Truck Handbook | ontario.ca https://share.google/9lpRhWcMHc8pmkKWv --- 1. GENERAL REQUIREMENTS Google Sheets, not Excel Auto-populate dates for 10 years starting from today Weekly structure: Sunday → Saturday Biweekly payroll system Date format in sheet must display like: Thursday, October 23 Must support split shifts (3 segments per day) Must enforce Ontario bus Hours-of-Service (HOS) rules Must apply my company’s pay rules, including Sundays, stats, sick days, spare rules, overtime rules, and appointments Color-coding should be changeable in a Settings tab, not locked All calculations must auto-update All summaries must auto-update Entire system connects to the Daily Log tab as the source of truth --- 2. SHIFT TYPE DROPDOWN (must be exactly these options) Booked Spare Overtime Sick Off Stat (Worked) Stat (Paid – No Work) Paid Legislative Day Unpaid Legislative Day Appointment --- 3. PAY RULES (this must be calculated automatically) Regular: uses my manually entered hourly rate (rate changes every few months) Sunday rules: Sunday ending ≤ 11:30 PM → 1.25× Sunday ending > 11:30 PM → 1.5× Overtime rules: Biweekly rule: anything over 80 hours in a pay period → 1.5× Spare day rule: if shift is Spare, hours over 8/day → 1.5× If shift is marked Overtime, pay at 1.5× no matter what If Overtime and Sunday, multiplier remains 1.5× (not stacked or increased) STAT holidays: Stat (Worked) → 1.5× for all worked hours Stat (Paid – No Work) → 7.5 paid hours Legislative Days: Paid → 7.5 paid hours Unpaid → 0 Sick Days: Paid at 90% of hourly rate Max 7.5 hours Spare Bonus: Spare shift automatically adds 0.5 hours paid for the day Appointments: When selecting Appointment, I must be able to manually enter a dollar amount for that day --- 4. HOURS-OF-SERVICE (MUST BE AUTO-VALIDATED) Daily rule flags: Max 13 hours driving Max 14 hours on-duty Max 16 hours elapsed from start of day Minimum 10 hours off-duty Cycle rule (14-day cycle): Max 120 on-duty hours in 14 days Must have 24 hours off within every 14 days Cycle reset requires 72 hours off The Daily Log must visually flag violations (red highlight + warning text) --- 5. TABS REQUIRED Tab Purpose Daily Log Source of truth: logs shifts, calculates hours, pay, and HOS Biweekly Pay Shows total hours, overtime, gross pay, net pay, and pay date Monthly Summary Hours + gross/net by month Yearly Summary Total yearly hours, overtime, gross, net Career Totals Lifetime hours and earnings across full 10 years Appointment Summary Filterable $ total of appointment days Charts & Visuals Graphs: overtime vs regular, yearly hours, cumulative net Settings Hourly rate, tax %, color settings, toggles --- 6. TAXES & NET PAY One field in Settings for tax % Net Pay = Gross − (Gross × Tax %) Show daily, biweekly, monthly, yearly, and career net totals --- 7. ADDITIONAL TRACKERS Sick day balance Spare balance Yearly hour total Career total hours Activity/notes field for each day HOS violation flag Biweekly net pay preview Track which shifts belong to which pay period --- 8. FORMATTING & VISUALS Sundays auto-highlight Stats auto-highlight HOS violations in red Spare in yellow Appointment in green Off in grey All colors must be manually changeable in Settings --- 9. WHAT I EXPECT WHEN COMPLETE A working Google Sheet that: Requires only daily inputs Automatically calculates all pay Enforces HOS rules Computes overtime correctly (daily + biweekly) Displays biweekly, monthly, yearly, and career totals Shows a 10-year financial and hours history Produces charts for visual insights --- Deliverable One completed Google Sheets file ready to use immediately. --- If you need clarification, I can answer questions — but ideally, I’ve provided enough detail for you to complete this smoothly. Please confirm: 1. Estimated timeline 2. Estimated cost 3. Whether you understand the pay rules and HOS rules as written Thank you, and I look forward to working with you. I don't mind getting on a discord call to explain and give examples