OĞUZ EROLAI·ADS·APPS

Project Timesheets: Billable Hours, Travel and Waiting Time, Approval and an Excel Setup

12 min read

In a project timesheet every hour answers three questions: which project, which task, and can it be billed to the customer. Without separate tasks for travel, waiting and warranty, the billable ratio looks higher than it is; leave them out entirely and possible overtime weeks are missed, because under some labour laws, Turkey’s among them, this time counts as working time even when nobody invoices it. Excel can do the arithmetic. It breaks on one file per technician, uncoded hours and a week missing when the month closes.

This page is for project managers and finance leads collecting hours in Excel at an installation and commissioning company.

What should a timesheet row carry?

A row is one person, one day, one project and one task. The routine I suggest is daily entry, weekly submission: technicians enter hours on the day and submit the week on Monday morning. With weekly batch entry, a Tuesday remembered on Friday collapses into “commissioning, 8 hours” and the travel and waiting split is lost.

The project code says whose work it was; the task code says what was done. The list used in the example:

CodeWorkBillable? (in the example)
MON · DEV · PRG · EGTInstallation, commissioning, PLC programming, customer trainingYes
BKLWaiting caused by the customerYes, because the contracts include a waiting clause
SYHTravelNo, covered by a fixed fee
GARWarranty workNo
İÇWorkshop preparation, office, internal trainingNo

(Codes kept from the Turkish original.) Warranty time goes under the original project’s code with the GAR task, not a new project, so the warranty load shows in that project’s own cost. Internal work goes to an internal project code such as “İÇ-ATL”, never to a blank.

In Turkey, Article 66 of Labour Act No. 4857 counts as daily working time the travel time when an employee is sent to work away from the workplace (b) and the time spent ready for work while waiting for it (c) (Labour Act 4857, mevzuat.gov.tr, read in Turkish on 2026-11-01). Article 41 defines overtime as work beyond 45 hours a week, and Article 63 allows weekly hours to be spread unevenly by agreement provided no day exceeds 11 hours. The example uses those numbers. Elsewhere, check your own rules; the point that non-billable hours are not unworked hours travels.

Example: one month of a 22-person commissioning team

The company, people, projects and all figures are fictional; I calculated and checked every derived number in a script.

An industrial automation commissioning company has 22 technicians and engineers; 3 project managers run 6 open projects. The example month has four full weeks (20 working days). Each technician keeps a personal Excel file; finance copies the 22 files into one table at month end. The task list has no GAR code; BKL exists, but the billable formula only sums MON, DEV, PRG and EGT.

The combined table holds 3,768 hours. The review finds:

  • 56 duplicated hours: for 7 technician-days (8 hours each) a team lead entered the crew’s hours in their own file as well; on those days a person’s total reaches 16–17 hours.
  • 262 hours with no project code: the project field is blank or free text such as “Plant line 2”. Asked, the owners place 157 as project work, 58 as travel and 47 as internal work.
  • 136 warranty hours and 86 travel hours were entered under the original project code as DEV.
  • 88 hours of customer-caused waiting were coded BKL, but the formula did not count them as billable.
  • 104 late hours: 4 technicians’ files had not arrived on invoicing day and were added afterwards; these hours are included in the 3,768. 77 of them are billable and were missing from the first invoice.

The cleaned total is 3,712 hours. The billable ratio changes like this:

Shown in ExcelCleaned
Billable hours2,5172,484
Total hours (denominator)3,506 (coded rows only)3,712
Billable ratio71.8%66.9%

The billable totals differ by only 33 hours, so nobody notices. The content differs: 278 of Excel’s 2,517 hours would have gone onto the wrong invoice (warranty charged to the customer, fixed-fee travel and a day counted twice), and 245 hours would never have been invoiced (uncoded project work and waiting).

At row level the same thing shows in the third week of the technician with ID T-07: Monday 4 hours travel and 5 hours commissioning, Tuesday 8 hours commissioning and 3 hours waiting, Wednesday 9 hours commissioning (the team lead added another 8 for the same day), Thursday 6 hours warranty on an older project and 4 hours commissioning, Friday 6 hours commissioning and 4 hours travel back, Saturday 3 hours in the workshop with a blank project code. Raw total 60, 52 after removing the duplicate; 35 billable, a weekly ratio of 67.3%.

Where do 22 separate timesheet files go wrong?

All four showed up in the example: rows missed or entered twice across separate files, uncoded hours, a file missing on invoicing day, and a 17-hour day that looks ordinary in one file. The setup below catches them at row level.

Staying in Excel: one shared timesheet with SUMIFS

Set up one shared table that everyone writes to. Columns on the Hours sheet:

A Date · B ID · C Project · D Task · E Hours · F Billable · G Check · H Status · I Service report no.

How many people can write at once, and what happens when the site has no connection, depends on your Excel version and storage; try it with two people in your own setup before rolling out. If collisions hold you up, see concurrent data entry.

C and D are picked from Data Validation lists. Name the open project codes ProjectCode and the billable tasks (MON, DEV, PRG, EGT, BKL) BillableTask. Formulas in English Excel:

F2: =IF(E2="","",IF(COUNTIF(BillableTask,D2)>0,E2,0))
G2: =IF(E2="","",IF(C2="","No code",IF(D2="","No task",
      IF(COUNTIF(ProjectCode,C2)=0,"Not in list",
      IF(SUMIFS($E:$E,$B:$B,B2,$A:$A,A2)>11,"Day > 11","")))))

G adds up the person’s rows for the day. T-07’s Wednesday totals 17, so both rows are flagged; the project manager decides which is the duplicate. The 11-hour Tuesday is not flagged, the Saturday row says “No code” (not “Not in list”; the fixes differ) and a blank row stays blank. A blank task makes F silently return 0; G flags it as “No task”. Eleven matches the daily limit in Article 63’s distribution rule, so I used it as the threshold. For what a longer day means legally, ask payroll and your accountant; of the 9 person-days flagged, 7 were duplicates and 2 were commissioning nights of 12 and 12.5 hours. A flag means “look at this row”.

SUMIFS(sum_range, criteria_range1, criteria1, …) takes the range to add first (Microsoft: SUMIFS, 2026-11-01). In SUMIF it comes third, so a formula copied between them adds the wrong column without an error.

On a Weekly sheet, put the ID in A and that week’s Monday in B:

C2: =SUMIFS(Hours!$E:$E,Hours!$B:$B,$A2,
      Hours!$A:$A,">="&$B2,Hours!$A:$A,"<"&($B2+7))
E2: =MAX(0,C2-45)

Column D uses the same formula with Hours!$D:$D,"SYH" added for travel, and again with "BKL" for waiting. The billable ratio on the Summary sheet is =SUM(Hours!F:F)/SUM(Hours!E:E): the denominator is all hours, not coded hours. Dropping T-07’s 3 uncoded hours from the denominator lifts that week from 67.3% to 71.4%.

Approval, locking and late hours

The rules I suggest:

  • Approval is split by project. A project manager approves only the rows on their own projects, so T-07’s week goes to three approvers: that project’s manager, the warranty approver and, for the Saturday İÇ row, the supervisor. GAR rows go to whoever holds the warranty budget, İÇ rows to the technician’s supervisor. Nobody approves their own row.
  • The period locks. On the second working day after month end the period closes, and invoices are prepared from locked data.
  • Late hours go to a separate list. Hours arriving after the lock are not written into the closed month; they become candidates for the next invoice. The example’s 77 billable hours would land here. Whether the customer accepts a late charge depends on your contract.
  • Approved rows are never deleted. A correction is a reversing entry plus a new entry, showing who changed what, when and why. Approved-record change control covers this rule.

Sheet protection makes changes harder but records nobody’s edits. Pending hours must not mix into approved totals either; put “Pending” or “Approved” in H, and make Summary =SUMIFS(Hours!F:F,Hours!H:H,"Approved")/SUMIFS(Hours!E:E,Hours!H:H,"Approved").

Does a timesheet show overtime?

A timesheet shows overtime candidates; it does not decide overtime, so column E on the Weekly sheet is “candidate hours”. Under averaging arrangements some weeks above the threshold are not overtime; in Turkey Article 41 says so for averaging under Article 63. Which hours are overtime, and how they are paid, is for payroll and your accountant.

Travel and waiting make the difference here. T-07’s week is 52 hours: 41 hours of work, 8 of travel and 3 of waiting. A sheet that counts only work shows this week under 45. In the example month 14 of 88 person-weeks exceed 45, by 79 hours in total; with travel and waiting removed, only 6 person-weeks and 22 hours remain visible.

When should a 22-file timesheet move to an application?

The deciding factor is not headcount but how many people enter and approve hours. A five-person team with one project manager and one shared table is well served by the setup above. If your service or ERP software already has a time module covering project, task and approval, and the field team actually uses it, do not build a second system.

Count these in your own data: how many hours arrived uncoded last month, how many files were missing on invoicing day, how many days exceeded 11 hours, how many warranty hours reached an invoice?

RouteWhenLimit
One file per person3–4 people, one project managerManual merging; double entry stays hidden
One shared table + SUMIFSOne approver, codes in a listFlags but does not stop; no lock history
Time module in service/ERP softwareProject, task and approval already thereField staff need user accounts
Web application to be builtSeveral project managers; warranty and late-hour rules specific to youRules must be agreed in writing first

In the application to be built: from timesheet to invoice

The functions below could be built; they are not a ready-made product. They can be set up to fit your needs, and the scope is agreed in writing in the first step.

  • Daily entry: project and task only from lists; a row with no code cannot be saved. Going over the daily threshold asks for a reason. Rows a team lead enters for the crew wait for the technician’s confirmation, so the example’s 56 duplicated hours would have been caught by this rule.
  • Approval queue: each project manager sees only their own projects’ rows; GAR rows go to a separate approver. Entry time is recorded automatically, so late hours separate themselves.
  • Period lock and invoice statement: project, task, billable hours and service report number in the statement that goes to the customer.
  • Overtime candidate list: weekly totals including travel and waiting go to payroll; the decision stays there.
  • Link to project cost: approved hours times the loaded hourly cost in project cost tracking feed the project report.

AI can suggest a project and task from an uncoded row’s description (“line 2 PLC test”); the suggestion stays out of totals until the project manager approves it. Only rows within the user’s permissions are sent to the model.

The server can be opened in your company’s name; the timesheet records are yours and can be exported to Excel or CSV at any time. For small and mid-sized teams there is no per-user licence; the running cost is the server and maintenance. The source code sits in a repository you can access, with run instructions, a dependency list and known limits.

The first meeting is paid. Prepare your task list, your approval order and the timesheet files of one closed month, and write to me through the contact page.

What I have not verified

  • I did not run the formulas in Excel; I mirrored them in a script with T-07’s week and edge cases (exactly 11 hours, blank code, code not in list, blank row). The SUMIFS argument order is from Microsoft’s page; I did not check IF, COUNTIF, MAX, SUM or the ">="& date criterion there.
  • I read Articles 41, 63 and 66 of the Turkish Labour Act, not the working-time regulation or averaging details. I make no ruling on overtime, pay or payroll anywhere.
  • Whether waiting and travel are billable depends on your contract; the classification in the example is fictional.

Frequently Asked Questions

Should hours be entered as decimals or as hours:minutes?

I suggest decimals in quarter-hour steps: 7 hours 45 minutes is 7.75. Excel stores 7:45 as a fraction of a day; mixed with decimals in one column, the total is silently wrong. Add a Data Validation limit of decimals between 0 and 24.

Does a timesheet need location or screen tracking?

Not for this scope. Self-entered project, task and hours are enough; if on-site arrival must be proven, the time on the customer-signed service report can be used.

How does the hours statement sent to the customer match the service report?

Each row carries the service report number (column I), and the invoice statement is grouped by that number. If the report and the timesheet disagree, the difference reaches the project manager before invoicing; which one prevails depends on the rule in your contract.

Back to top ↑
WHAT YOU CAN ASK FOR

How do I work?
Work-hour packages

Tell me about your business and what you need on WhatsApp or by phone; we agree on a work block of somewhere between 5 and 40 hours and I start doing what is needed. What you get is not a calendar window — it is net working hours spent directly on your business. I can use 40 hours working three days straight, or deliberately spread it out and finish it over a year — whatever the work calls for. I do not spend my time estimating how long something will take, I spend it doing the work; every hour I spend leaves a lasting result in your business. We keep going for as long as you find it useful.

WORK BLOCKS

Why not monthly?
Is 40 hours a week?

On a fixed monthly fee both sides are watching the wrong thing: you watch a fixed cost, the other side watches how many more contracts like this they can line up. Working by the hour puts us both in front of the same thing — the real value of the work that got done. I know not having an upfront answer to "how much will this cost in total?" is the natural cost of this model. But instead of paying a fixed fee for months to a setup that is not producing results, you only pay for the qualified time spent on your work — and you see exactly where that time went, start to finish. That is why my clients keep coming back.

5hours300€ + VAT60€ per hourChoose this block
10hours500€ + VAT50€ per hourChoose this block
20hours800€ + VAT40€ per hourChoose this block
40hours1,400€ + VAT35€ per hourChoose this block