Google Sheets timesheet template for freelancers, with formulas
A timesheet for billing needs more than hours: it needs the client, the rate, the rounding you agreed on and a total per client per month. Below is the layout that works for most freelancers and consultants, the exact formulas to type in Google Sheets, and the traps that break totals. At the end, a ready-made sheet that fills itself from Google Calendar.
The columns a billing timesheet needs
| Column | Content | Format |
|---|---|---|
| A | Date | Date |
| B | Start | Time |
| C | End | Time |
| D | Duration | Duration, [h]:mm |
| E | Billed duration, after rounding | Duration, [h]:mm |
| F | Client | Text |
| G | Hourly rate | Currency |
| H | Amount | Currency |
| I | Notes | Text |
Keep one line per meeting or work block. Totals go in a separate tab, so sorting and filtering the lines never breaks them.
The formulas, line by line
These go in row 2 and can be filled down. Google Sheets stores times as fractions of a day, which is why some formulas multiply by 24.
- Duration in D2:
=MOD(C2-B2, 1). TheMODkeeps the result right when a session goes past midnight. - Billed duration rounded up to 15 minutes in E2:
=CEILING(D2, TIME(0, 15, 0)). UseTIME(0, 6, 0)for tenths of an hour. - With a 30-minute minimum, E2 becomes:
=MAX(CEILING(D2, TIME(0, 15, 0)), TIME(0, 30, 0)). - Amount in H2:
=E2*24*G2. Without the*24, a two-hour meeting at 80 would bill 6.67. - Hours as a decimal, for invoices that ask for 2.25 rather than 2:15:
=E2*24.
If your sheet is set to a European language, formulas use a semicolon instead of a comma: =CEILING(D2; TIME(0; 15; 0)).
Totals per client and per month
In a separate tab, one line per client:
- Hours for Acme in October 2026:
=SUMIFS(Lines!E:E, Lines!F:F, "Acme", Lines!A:A, ">="&DATE(2026,10,1), Lines!A:A, "<"&DATE(2026,11,1)), formatted[h]:mm. - Amount for Acme in October: the same formula on column H.
Write the month bounds as >= the first day and < the first day of the next month. That way the last day is never cut off, whatever the time of the last meeting.
Four traps that break a timesheet
- Totals shown modulo 24 hours. With the format
h:mm, 26 hours displays as 2:00. Always use[h]:mmfor durations. - Times typed as text. A time pasted from an email may be text: it sits on the left of the cell and is ignored by sums. Retype it, or wrap it in
=TIMEVALUE(). - Rounding the total instead of each line. With rounding up to 15 minutes, 4 meetings of 20 minutes bill 2:00 if you round each line, or 1:30 if you round the total. Say which one in your quote and apply it the same way every month.
- Editing last month after invoicing. Copy each month's totals to a closed tab once you have invoiced, so a late change to a line cannot alter a sent invoice.
Filling the template from Google Calendar
Typing every meeting twice, once in the calendar and once in the sheet, is where most timesheets go wrong. If your meetings are in Google Calendar, export them instead: the export guide compares the built-in export, a small script and an add-on. To tag each line with its client, the guide to counting hours by client explains colours, calendars, title words and guest domains.
A template that fills itself
The free Calendar Timesheet sheet does this work for you. Its free export writes any period of your calendar into a tab, one line per event, with totals by colour and by calendar. The Pro trial adds clients, rates, rounding and minimums, fills these columns every hour and closes each month into one statement per client.