Last updated: 12 September 2026
Download the Free ESFA-Compliant Spreadsheet
If you need an immediate working spreadsheet for your tutors, assessors, or apprentices, start with our free OTJ hours tracker template. The CSV opens natively in Excel and Google Sheets with pre-configured formulas for planned vs actual hours, part-time pro-rata calculations, and pace deficit alerts.
1. The Statutory ESFA OTJ Calculation Formula
Under current DfE and ESFA Apprenticeship Funding Rules, off-the-job training is quantified based on a baseline minimum of 6 hours per week across planned programme duration:
Total Planned OTJ Hours = (Planned Weeks on Programme - Statutory Annual Leave Weeks) × 6 hours/weekExample for 12-month programme (52 weeks - 5.6 weeks statutory leave = 46.4 working weeks):
46.4 weeks × 6.0 hours = 278.4 Minimum Planned OTJ Hours
2. What Fields Are Included in the Free Template?
The downloadable tracker is structured to satisfy ESFA compliance audits and EPA gateway declarations:
| Column Header | Formula / Data Type | Audit Function |
|---|---|---|
| Activity Date | Date (DD/MM/YYYY) | Proves activity occurred within active apprenticeship agreement dates. |
| Activity Category | Dropdown List | Categorises delivery: Mentoring, Shadowing, Masterclasses, Vendor Training. |
| Actual Hours Logged | Decimal (e.g. 3.5) | Aggregates total completed training hours into cumulative sum. |
| Target KSB Reference | Text (e.g. K1.2, S3.1) | Proves training directly developed curriculum occupational standard competencies. |
| Pace Deficit / Surplus | =Actual - PlannedToDate |
Conditional formatting turns red if learner falls >10 hours behind target pace. |
| Supervisor Verification | Boolean / Timestamp | Fulfills mandatory workplace mentor endorsement requirement. |
3. When to Graduate from Excel to Automated Software
While an Excel template is an essential tool for managing a handful of apprentices, running a cohort of 50+ learners on standalone spreadsheets creates severe operational risks:
- Lost Evidence Links: Apprentices fail to link photos or reflections to spreadsheet rows.
- Retroactive Batching: Learners forget to update spreadsheets until days before quarterly reviews.
- No Real-Time Employer Sign-Off: Chasing supervisors for email approvals leads to weeks of unverified hours.
Modern platforms like TIQPlus eliminate spreadsheet chasing entirely: apprentices log evidence and OTJ hours in 10 seconds from their mobile phones, and supervisors confirm with a 1-tap WhatsApp reply.
Automate Your OTJ Compliance with TIQPlus
Eliminate manual spreadsheet reconciliations and protect your contract from ESFA funding clawbacks with real-time mobile tracking.
Frequently asked questions
How do I calculate statutory Off-The-Job (OTJ) hours in Excel?
Under ESFA funding rules, the statutory formula for full-time apprentices (30+ hours/week) is 6 hours per week multiplied by planned weeks on programme minus statutory annual leave (typically 46.4 working weeks per year = 278 hours minimum per year). For part-time learners working under 30 hours per week, the programme duration must be extended proportionally. The downloadable template calculates this automatically.
Can I open the OTJ tracker template in Microsoft Excel and Google Sheets?
Yes. The download is an open UTF-8 CSV spreadsheet with standard formulas. You can open it natively in Microsoft Excel, import it into Google Sheets, or upload it to your institutional SharePoint without compatibility issues.
What must be recorded for an OTJ activity to pass an ESFA audit?
To satisfy ESFA compliance auditors, every OTJ record must include: date of activity, start and finish time, actual hours spent, delivery mode (e.g., direct coaching, workplace mentoring, manufacturer training), standard KSB mapped, brief description of new learning acquired, and contemporaneous employer/tutor verification.