Pro workbook: Log, Projects, Week, Setup, Formulas. Sample data, real formulas.
| Time log | |||||||||||
| One row per block of work: date, project from the dropdown, in, out, break. Hours and amount are calculated. | |||||||||||
| Date | Project | In | Out | Break (h) | Hours | Billable | Amount | Note | Total hours | 54.67 | |
| 2026-09-07 | Northwind onboarding | 09:00:00 | 12:30:00 | 0 | 3.50 | yes | 332.50 | Flows review | Billable hours | 49.67 | |
| 2026-09-07 | Internal | 13:00:00 | 14:00:00 | 0 | 1.00 | no | Standup, planning | Billable amount | 5,099.58 | ||
| 2026-09-07 | Northwind onboarding | 14:00:00 | 17:30:00 | 0 | 3.50 | yes | 332.50 | KYC states | Billable share | 90.9% | |
| 2026-09-08 | Halden portal | 08:45:00 | 12:00:00 | 0 | 3.25 | yes | 357.50 | Research synthesis | |||
| 2026-09-08 | Halden portal | 12:45:00 | 18:10:00 | 0.25 | 5.17 | yes | 568.33 | Prototype | |||
| 2026-09-09 | Northwind onboarding | 09:15:00 | 13:00:00 | 0 | 3.75 | yes | 356.25 | Save & resume | |||
| 2026-09-09 | Sales calls | 14:00:00 | 15:00:00 | 0 | 1.00 | no | Intro call, Tessera | ||||
| 2026-09-09 | Northwind onboarding | 15:00:00 | 17:00:00 | 0 | 2.00 | yes | 190.00 | Emails | |||
| 2026-09-10 | Halden portal | 09:00:00 | 19:30:00 | 1 | 9.50 | yes | 1,045.00 | Usability test day | |||
| 2026-09-11 | Northwind onboarding | 09:00:00 | 12:00:00 | 0 | 3.00 | yes | 285.00 | Handover doc | |||
| 2026-09-11 | Internal | 13:00:00 | 16:00:00 | 0 | 3.00 | no | Friday learning | ||||
| 2026-09-14 | Halden portal | 09:00:00 | 17:00:00 | 0.5 | 7.50 | yes | 825.00 | Design system | |||
| 2026-09-15 | Northwind onboarding | 09:00:00 | 18:00:00 | 0.5 | 8.50 | yes | 807.50 | Compliance review | |||
| Formulas read rows 5–500. Raise the number in the Projects and Week formulas to log more. | |||||||||||
| The 13 rows are examples — delete them. | |||||||||||
| By project | |||||
| Hours and billable amount per project, from the Log. | |||||
| Project | Hours | Share | Rate/h | Amount | Entries |
| Northwind onboarding | 24.25 | 44.4% | 95.00 | 2,303.75 | 6 |
| Halden portal | 25.42 | 46.5% | 110.00 | 2,795.83 | 4 |
| Internal | 4.00 | 7.3% | 2 | ||
| Sales calls | 1.00 | 1.8% | 1 | ||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| 0.0% | |||||
| Total | 54.67 | 5,099.58 | 13 |
| By week | |||||
| Hours per week from the Log, overtime above the Setup threshold, billable amount. | |||||
| First week (Monday) | 2026-09-07 | ||||
| Week of | Hours | Overtime | Billable | Amount | Days worked |
| 7 Sep | 38.67 | 33.67 | 3,467.08 | 5 | |
| 14 Sep | 16.00 | 16.00 | 1,632.50 | 2 | |
| 21 Sep | |||||
| 28 Sep | |||||
| 5 Oct | |||||
| 12 Oct | |||||
| 19 Oct | |||||
| 26 Oct | |||||
| 2 Nov | |||||
| 9 Nov | |||||
| 16 Nov | |||||
| 23 Nov |
| Setup | ||
| Projects with an hourly rate and whether they bill; the overtime rule. | ||
| Overtime after (h/week) | 40 | |
| Project | Rate/h | Billable |
| Northwind onboarding | 95.00 | yes |
| Halden portal | 110.00 | yes |
| Internal | 0.00 | no |
| Sales calls | 0.00 | no |
| Up to 20 projects. The Log dropdown and the Projects sheet read this list. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Log · Hours | IF(OR(C5="",D5=""),0,(D5-C5+IF(D5<C5,1,0))*24-N(E5)) | Times are fractions of a day; out − in × 24 gives hours. If out is earlier than in (a night shift), a day is added first. N() turns an empty break into 0. Empty rows give 0, hidden by the number format. |
| Log · Billable | IFERROR(INDEX(Setup!$C$6:$C$25, MATCH(B5, Setup!$A$6:$A$25, 0)), "?") | Looks the project up on Setup; a project that isn't there shows ? in red. |
| Log · Amount | F5 * INDEX(Setup!$B$6:$B$25, MATCH(B5, Setup!$A$6:$A$25, 0)) | Hours × the project's rate. Non-billable projects have a rate of 0. |
| Projects · Hours | SUMIF(Log!$B$5:$B$500, A5, Log!$F$5:$F$500) | All logged hours for the project. |
| Week · Hours | SUMIFS(Log!$F$5:$F$500, Log!$A$5:$A$500, ">="&A6, Log!$A$5:$A$500, "<="&A6+6) | Hours whose date falls in the seven days from the week's Monday. |
| Week · Overtime | MAX(0, B6-Setup!$B$3) | Hours above the weekly threshold. |
| Week · Days worked | SUMPRODUCT((dates in week)*(hours>0) / COUNTIF(dates, dates&"")) | Counts distinct dates in the week: each row is weighted 1 ÷ how many rows share its date, so a day with three entries counts once. |