Comprehensive Excel: Attendance & Trip Tracker

1 excel: Trip Monitoring Sheet> already made one but its bad(attached)

This sheet is used to track every trip a TA makes on a specific shift. Here’s what I need:

Input Features:
• Tab for each date (preferably auto-filled or templated for reuse).
• Fields:
• TA Name (dropdown, fixed list to be used across both sheets)
• Train Set (dropdown)
• Station (dropdown: 2G1 or 2A1)
• Shift (dropdown)
• Time of trip

Summary Tab (automated):
• Show each TA only once per date.
• Count of trips from 2G1
• Count of trips from 2A1
• Calculate loops completed: 1 loop = 1 trip from 2G1 + 1 from 2A1
• Allow date filtering
• Clean layout, no duplicate entries

Dashboard (Trip Monitoring):
• Total loops completed (per day/shift)
• Most active TA
• TAs with low loop count (below threshold)
• Station-based activity breakdown
• Trend chart if possible

⸻

2 excel: Attendance, Uniform & Compliance Tracker

This sheet tracks daily staff presence, lateness, uniform, and more.

Input Tab:
• Tab for each date (like Sheet 1).
• Fields:
• TA Name (same dropdown list )
• Shift (dropdown:
• E = 05:45
• M = 10:00
• L = 13:45)
• Actual Arrival Time (manual input)
• Formula to calculate minutes late
• Field to input overtime hours (optional)
• Uniform (Yes/No dropdown)
• Absence flag (checkbox or dropdown)
• RTW (Return-to-Work):
• If Absent = “Yes”, RTW should auto-show “Pending”
• Manually click to mark it “Completed”

Summary Tab (automated):
• Total days present per TA
• Count of late arrivals
• Count of uniform issues
• Count of absences
• RTW status tracker (Pending vs Completed)

Dashboard (Attendance & Compliance):
• Total late arrivals per TA
• Total absences per TA
• Uniform compliance rate
• Daily attendance summary
• RTW status snapshot
• Filters by date or TA

⸻

Additional Requirements:
• Should be user-friendly, even for non-Excel users.
• Use Data Validation dropdowns wherever possible.
• Clean, non-buggy formulas (no #SPILL! or #REF! errors).
• Scalable design: I want to use this sheet for months ahead, not just one day.

⸻
Access & Permissions:
• Trip Monitoring Sheet will be stored in a shared folder and editable by the full team (TLs and CSS).
• Attendance & Compliance Sheet
must have editing rights limited to just me and another CSS, TLs will only review — they should not be able to change anything.
• Please protect sheets or lock ranges as needed.


Please reach out with your proposal, timeline, and example work if possible. If you understand dashboards well, that’s a big plus.

Looking forward to working with someone reliable and detail-oriented!