Timesheet

Hours derived from start and end times, billable value per project, and a shift over midnight that does not break it

✓ No account, no email · ✓ Excel 2016+ and Google Sheets · ✓ Free for commercial use

Timesheets are abandoned when they ask for the same number twice. This one asks for a start and an end and derives the hours, which is both faster to fill in and harder to fudge. The number worth watching is not total hours but utilisation — billable over total — and it sits in one cell rather than needing a pivot table. A shift that crosses midnight is handled, which is where most timesheet templates quietly produce a negative.

What the worksheet looks like

The actual columns, with sample rows

Timesheet: hours and amount are derived, everything else is typed.
A	B	C	D	E	F	G	H	I
1	Date	Project	Start	End	Break	Hours	Billable	Amount
2	2026-01-14	Acme dashboard	09:00	12:30	0.25	3.25	Yes	276.25
3	2026-01-14	Internal	14:00	15:00	0.00	1.00	No	0.00
4	2026-01-15	Acme dashboard	22:00	01:00	0.00	3.00	Yes	255.00

The formulas that do the work

Why each one is written the way it is

=IF(D5="","",(E5-D5+IF(E5<D5,1,0))*24-F5) Hours worked. Times are stored as fractions of a day, hence the *24. The inner IF adds a whole day when the end is earlier than the start, so a 22:00 to 01:00 shift gives 3 hours rather than minus 21.
=IF(H5="Yes",G5*I5,0) Billable amount. Non-billable time still records its hours, which is the point — you cannot compute utilisation from billable rows alone.
=SUMIFS(G5:G204,H5:H204,"Yes") Billable hours across the sheet, matched on the Yes/No column.
=IFERROR(M5/M4,0) Utilisation. Guarded, so a sheet with no rows yet shows 0% instead of an error.

A week that looked full and billed 62%

A worked example with real numbers

Forty-one hours logged across five days, which feels like a full week. The billable total is 25.5 and utilisation reads 62%. The By project tab shows where the rest went: 9 hours on internal work and 6.5 on a project that was quoted fixed-price and has now run over. Neither is visible from a total hours figure, and both change what you do next week.

What is in the workbook

Every tab, and what it holds

Every tab in the workbook and what it is for.
Tab	Contents
README	What to fill in and what is calculated, with the colour key.
Timesheet	The log: 200 rows, with total hours, billable hours, value and utilisation beside it.
By project	Hours, billable hours and value per project. Type each project name once.

Features and related templates

What is included, and what to look at next

What it does

Hours derived from start and end times
Shifts crossing midnight handled correctly
Billable value and utilisation in one cell each
Per-project rollup, 200 rows
Excel 2016+ and Google Sheets

Related templates

Need it adapted?

Built around your own data and column names
Connected to your source system
Delivered within 24 hours

Get your Excel job done

Questions about this template

The ones that come up most

How do I enter a break?

In decimal hours, not as a time: 0.25 for fifteen minutes, 0.5 for half an hour. The break is subtracted after the start-to-end difference is converted to hours.

Can different projects have different rates?

Yes, the rate is on the row rather than fixed for the sheet. Put the rate that applies to that block of work on its own line; the By project totals follow.

Why does my overnight shift show the right hours?

Because the formula adds a day when the end time is earlier than the start. Templates that just subtract one time from the other return a negative for any shift crossing midnight.