The quickest way to get an Excel flexi timesheet in the UK is to build your own using a simple layout: Date, Start Time, Finish Time, Break (minutes), and a running balance column that compares your worked hours to your contractual daily hours. A basic formula like =IF(D2="","",(C2-B2)*24-D2/60) calculates daily hours worked, and a cumulative SUM formula tracks your running flexi balance. This article explains how to set that up, what to watch for with UK working time rules, and how to make the sheet accurate enough for payroll or HR sign-off.
Why a Flexi Timesheet Needs to Be UK-Specific
UK employers that offer flexible working hours (flexi) usually operate a “core hours” system: you must be present between, say, 10:00 and 16:00, and you can start as early as 07:00 or finish as late as 19:00. The timesheet must therefore record actual start and finish times, not just total hours, so the manager can verify core-hour coverage. It also needs to handle the UK statutory working week: the Working Time Regulations 1998 cap the average working week at 48 hours (unless the worker has opted out), and require minimum rest breaks of 20 minutes for shifts over 6 hours. A well-designed flexi sheet will show daily hours, weekly totals, and a running average so both the employee and the employer can see if the 48-hour average is being approached.
Core Structure: Columns and Layout
Set up your sheet with these columns in row 1:
- A: Date
- B: Start Time (e.g., 08:30)
- C: Finish Time (e.g., 17:00)
- D: Break (minutes, e.g., 30)
- E: Contractual Hours (e.g., 7.5)
- F: Daily Flexi (positive or negative)
- G: Running Balance
Enter times as actual Excel time values (e.g., 08:30), not text. For the daily flexi formula, use =IF(COUNT(B2:C2)=2,(C2-B2)*24-D2/60-E2,0). This calculates the difference between finish and start in hours, subtracts the break in hours, then subtracts your contractual hours. The result is the flexi gained or lost that day (e.g., 0.25 means 15 minutes gained). In the running balance column, use =G1+F2 (with G1 as the opening balance from the previous month, or 0). Format columns B and C as time (e.g., hh:mm), and columns E, F, G as numbers with 2 decimal places.
Adding a Weekly and Monthly Summary
Most UK flexi schemes settle the balance monthly, so add a summary block below the daily entries. For example, in row 34 (after 31 daily rows), put:
- Total Hours Worked:
=SUM((C2:C32-B2:B32)*24-D2:D32/60)entered as an array formula (Ctrl+Shift+Enter in older Excel, or just Enter in Microsoft 365). - Contractual Hours for the month:
=COUNT(A2:A32)*7.5(adjust the 7.5 to your daily hours). - Monthly Flexi Balance:
=Total Hours Worked - Contractual Hours.
If you use Excel for Microsoft 365, the array formula works without Ctrl+Shift+Enter. On a Mac, press Command+Shift+Enter for older versions.
Handling Breaks and Unpaid Time
A common mistake is recording breaks as a start/end time instead of a duration. If you enter break start and end in two columns, the formula becomes more complex. Simpler: record break duration in minutes in one column. For a lunch break from 12:30 to 13:00, enter 30. For a 15-minute morning break, enter 15. The formula subtracts the total break minutes. If you take an unpaid break that is not a rest break (e.g., a long lunch), still record it – the sheet should reflect all time away from work. For shifts over 6 hours, UK law requires a 20-minute rest break, so your sheet should flag if the break column is less than 20 for such days. You can use conditional formatting: select the break column, go to Home > Conditional Formatting > Highlight Cells Rules > Less Than, and enter 20.
Common Problems and How to Fix Them
Three issues come up often:
- Negative flexi balances: If you work fewer hours than contracted, the daily flexi goes negative. That’s normal – the running balance shows how much you owe. Don’t hide it; it’s the point of the sheet.
- Times crossing midnight: If you finish after midnight (e.g., start 22:00, finish 06:00), the simple subtraction
(C2-B2)*24returns a negative number. Use=IF(C2<B2,(C2+1-B2)*24,(C2-B2)*24)to handle overnight shifts. - Rounding errors: Excel stores times as fractions of a day, so 8 hours shows as 0.3333. Multiplying by 24 gives decimal hours, but rounding to 2 decimal places can cause small discrepancies. Use
=ROUND(...,2)in the summary formulas to keep totals consistent.
Comparing Excel to Other Flexi Tools
Excel is flexible and free if you already have Office, but it lacks automation. A dedicated UK flexi app like FlexiTime or ClockInEasy automatically calculates balances, handles overtime rates, and syncs with payroll. However, those often cost per user per month. Excel’s advantages: no subscription, full control over formulas, and easy customisation. Its disadvantages: manual data entry, risk of formula errors, and no automatic alerts for approaching the 48-hour average. If your employer requires a specific format, check with HR before building your own.
Costs and Alternatives
The only cost is your time: Excel is part of Microsoft 365 (from about £7 per month for personal, or included with many business plans). Free alternatives include Google Sheets, which has similar formulas but uses =ARRAYFORMULA for array calculations, and LibreOffice Calc, which is free and handles most Excel formulas. For a ready-made template, sites like Vertex42 offer free UK-style timesheets, but they often require you to adapt the formulas. If you want a zero-maintenance solution, consider a simple paper timesheet or a basic online time tracker – but those won’t give you the running flexi balance automatically.
Safety and Accuracy Tips
Because flexi balances affect pay and leave, accuracy matters. Always use 24-hour time format to avoid AM/PM confusion. Double-check that your break entries are in minutes, not hours. Protect the formula cells by selecting them, going to Format Cells > Protection, ticking “Locked”, then enabling sheet protection (Review > Protect Sheet). This prevents accidental overwrites. Keep a backup of the previous month’s sheet before starting a new one. And if your employer uses a specific flexi policy (e.g., maximum carry-over of 2 days), add a cell that shows your balance in days (divide the monthly balance by your daily hours) and use conditional formatting to flag if it exceeds the cap.
Frequently Asked Questions
Can I use this sheet for a 37.5-hour week? Yes. Change the contractual hours per day in column E. For a 37.5-hour week over 5 days, that’s 7.5 per day. The formulas work for any daily hours.
How do I account for bank holidays? If you don’t work a bank holiday, leave the row blank or enter 0 in the break column and 0 in the contractual hours column. Alternatively, add a column for “Holiday” and subtract those hours from the monthly contractual total. Most UK schemes treat bank holidays as paid leave, so they don’t affect flexi.
What if I work from home and my hours vary? The sheet works the same. Just record your actual start and finish times, regardless of location. Some UK employers require a note in the remarks column if you start later than usual due to a doctor’s appointment – add a “Notes” column for that.
Does the 48-hour average include overtime? Yes, the Working Time Regulations count all hours worked, including overtime, unless you have a signed opt-out. Your sheet should include all hours, so the running average will show if you’re approaching the limit.
Can I use this in Google Sheets? Yes, the formulas are nearly identical. In Google Sheets, use =ARRAYFORMULA(SUM(...)) for array calculations, and the IF function works the same. The main difference is that Google Sheets uses File > Settings for calculation options, and you don’t need Ctrl+Shift+Enter.
Related
- Excel and Access Training: What You Actually Need
- Convert KMZ to Excel: 4 Ways That Actually Work
- DepEd Class Attendance Sheet Template Excel: Free Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Out of Office in Outlook on the Web: Automatic RepliesTo set an out-of-office message in Outlook on the web, open Settings, find Automatic replies, turn them on,…
- Unknown Range Name in Google Sheets: Causes and Fixes“Unknown range name” in Google Sheets means a formula contains a word or reference that Sheets is treating…
- Excel Remove Duplicates: Does It Keep the First Row?Yes. In Microsoft Excel for Microsoft 365, Remove Duplicates keeps the first matching row in the current top-to-bottom…
- How to Insert a Clickable Table of Contents in WordTo insert a clickable table of contents in Word, apply built-in Heading styles to your section titles, then…
- Excel Find and Replace in Selected Cells: How to Do ItTo use Find and Replace only in selected Excel cells, select the range first, press Ctrl+H, enter the…
- Clean Data Button in Excel: Where It Is and How It WorksIn Excel for the web, select your data and look on the Data tab for Clean Data. The…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.