Why log hours at all
People track project hours for a handful of reasons: to bill a client, to understand where the week actually goes, to compare effort against a plan, or simply to be honest with themselves about how long things take. A spreadsheet is a good place to start because it costs nothing extra, can be shaped to your needs and produces totals with a few formulas.
Decide your purpose first, because it shapes the detail you need. Billing may require task descriptions and a client name. Personal awareness may need only a project and a duration.
The simplest layout: one row per work session
The most flexible structure is a log with one row for each stretch of work. A typical set of columns:
| Date | Project | Task | Start | End | Hours |
|---|
- Date: the day the work happened.
- Project: chosen from a list, so names stay consistent.
- Task: a short note on what you did.
- Start and End: optional, if you note times.
- Hours: calculated from start and end, or typed directly if you prefer to log durations.
Add a column for “Billable” (yes or no) if it matters, and perhaps one for a client or category. Keep to the columns you will actually fill in.
Each row is a record, and records are never overwritten, only added. This pattern follows the general approach in spreadsheets for everyday planning and tracking.
Working out hours from times
If you record start and end times, the sheet can calculate the duration. Most tools store times as fractions of a day, so subtracting start from end gives a fraction that you then multiply by 24 to get hours. For example, a session from 9:00 to 11:30 lasts 2.5 hours. Another from 13:15 to 14:45 lasts 1.5 hours. Together they make 4 hours.
Watch for common pitfalls:
- Sessions that cross midnight will give a negative result unless the formula accounts for it.
- Unpaid breaks need a separate column or a separate row.
- Mixed formats, such as some times typed as text, produce errors. Rules like those in data validation can restrict entries to real times.
Some people find durations easier than clock times. Typing “1.5” for an hour and a half is quick, but be consistent about whether you mean decimal hours or hours and minutes. 1.5 hours is 90 minutes, whereas “1:30” read as a duration is the same amount; “1.30” typed as decimal hours would be about 78 minutes, which is a classic source of error.
Totals by project and by week
Once you have a log, summaries come from formulas rather than manual adding.
- Total by project: a conditional sum, adding the Hours column where Project equals a given name.
- Total by week: a conditional sum on a date range, or a helper column that computes a week number from the date.
- Billable hours: a conditional sum where Billable is yes.
Here is a hypothetical week. Suppose the log shows Project A: 3 + 2.5 + 1.5 = 7 hours, Project B: 4 + 1 = 5 hours and Admin: 2 hours. The week’s total is 7 + 5 + 2 = 14 hours, and Project A makes up 7 of 14, which is half. These numbers are invented, but checking that project totals add up to the grand total is a good habit for your real data.
Pivot tables can produce these summaries without writing formulas, and they refresh as new rows are added. Learning a few spreadsheet formulas still pays off for the fixed totals you report.
A weekly-grid alternative
Some people prefer a grid with projects down the side and days across the top, entering hours in each cell. It is quick to fill in and easy to read, and row and column totals are simple sums. The trade-off is less detail: you cannot record several tasks in one cell, and it is harder to filter or summarize across weeks. A grid suits a person with a few stable projects; a log suits varied work or billing that needs detail.
You can combine them: keep the log as the source of truth and build the weekly grid from it with a summary formula.
Make logging a habit, not a reconstruction
The most accurate time records are made at the moment of work. Reconstructing a week from memory on Friday tends to be inaccurate, because people usually forget small tasks and interruptions. A few suggestions:
- Log at natural breaks, such as when you switch projects or finish a task, which is also when context switching costs show up.
- Use an end-of-day moment, such as a short shutdown routine, to fill gaps while memory is fresh.
- Use a weekly review to check totals and spot entries that look wrong.
- Keep the sheet open or one click away, so logging is easy.
If you log time for others, agree in advance on rounding rules, such as to the nearest quarter hour, and apply them consistently. State the rule in the sheet.
Using the data to plan better
Hours logged are useful beyond billing. Comparing actual time with what you expected helps you improve future task estimates. If several jobs of one type each took noticeably longer than planned, that is useful information for the next quote. Keep conclusions modest, though: a few weeks of data show patterns but do not prove them, and unusual weeks happen.
Be cautious with time data about other people. If a manager uses a sheet to compare staff hours, the numbers can be misunderstood or can encourage logging for appearances. Hours worked is not the same as value produced, a distinction that matters when thinking about employee productivity.
Keeping the sheet healthy
- Protect formulas. Mark the cells where people type and lock the totals, especially if a team shares one file.
- Use a project list. A drop-down prevents “Proj A,” “Project A” and “project a” appearing as three projects.
- Start a new tab or file each year or period, so the log stays fast and totals are simple to read.
- Save copies. A year of hours is worth a backup.
When to move to a dedicated tool
A sheet is enough for one person or a very small team with straightforward projects. Consider a dedicated time-tracking tool when you need timers, invoicing, approvals, many people entering time at once, or reports that would be tedious to build. If you do move, export your sheet first, clean it, and check that totals match before relying on the new system.