Guide, 2026-11-04

How to track freelance hours and invoices in a spreadsheet

Most freelancers do not lose money because their rate is wrong. They lose it to hours that never make it onto an invoice and invoices that nobody chases. A single spreadsheet can fix both, as long as the time log and the invoices are connected. Here is a method that works in any spreadsheet app.

Step 1: one time log, one row per entry

Create a sheet with these columns:

Log non-billable time as well. It is the only way to see how much of your week actually earns money.

Step 2: unbilled work is a filter

Unbilled work is every billable row with a blank invoice number. A SUMIFS on amount by client, with billable equal to "Yes" and invoice number equal to "", gives you a running total of money you have earned but not yet asked for. Check it every Friday.

Step 3: build the invoice from the log

When you bill, type the invoice number into the rows you are charging for. You can bill some entries and leave others for later. The invoice sheet then pulls every row with that number using FILTER (or a helper column and INDEX in older versions), adds the lines for the subtotal, applies tax if you charge it, and shows the total. Because the invoice is built from the log, the two can never disagree.

Step 4: keep an invoice register

A second small list holds one row per invoice: number, client, invoice date, due date (invoice date plus your terms), total and paid date. Status is a simple rule: if there is a paid date, "Paid"; if today is after the due date, "Overdue"; otherwise "Due". Days late is today minus the due date for overdue invoices.

Step 5: watch project budgets and your real rate

For fixed-scope projects, compare hours logged with the hours you quoted. Flag projects at around 80% of budget so you can talk to the client before you run over, not after. Then calculate two rates: your average billed rate (billable amount ÷ billable hours) and your effective rate (billable amount ÷ all hours logged). The gap between them is the cost of unpaid time.

A worked example

A freelance designer charges Client A $80 an hour and Client B $95 an hour. Here is one week:

Total hours: 17.5. Billable hours: 14.5, so the billable share is about 83%. Billable amount: $1,287.50.

The designer invoices Client B for its 8.5 hours. The subtotal is $807.50. If they charge a 20% sales tax or VAT, tax is $161.50 and the invoice total is $969.00. Client A's $480.00 stays in unbilled work until the end of the month.

The average billed rate is $1,287.50 ÷ 14.5, about $88.79. The effective rate across all 17.5 hours is about $73.57. If the brand guide was quoted at 20 hours, 8.5 used means 42.5% of the budget is gone.

Common mistakes

• Logging time in hours and minutes (1:30) and then multiplying it by a rate, which gives the wrong amount in most spreadsheets.
• Writing invoices in a separate document, so the log and the invoices drift apart.
• Reusing or skipping invoice numbers. Many countries expect a unique, sequential series.
• Assuming every invoice needs the same fields everywhere. Required details such as tax numbers vary, so check local rules.
• Never looking at the effective rate, so a client who generates lots of unpaid calls looks as profitable as one who does not.

When a ready-made workbook saves time

Everything above can be built by hand, but the invoice layout, rate lookups, status rules and budget warnings take a while to get right and are easy to break. FreelanceBook is built around the same method: hourly time and expenses by project, an unbilled view, a printable invoice for any invoice number, an invoice register with Due, Overdue and Paid, project budgets and a dashboard. It uses one currency and one tax rate and is for record keeping, not tax advice.