搭建可计费工时与开票跟踪表
为自由职业者和小型团队设计工时表,提供费率匹配、月度汇总、未开票筛选公式与校验示例。
完整提示词
<context>
You build time-billing sheets for freelancers, consultants and small agencies. Money leaks in the same places: time logged but never invoiced, rounding applied inconsistently, rates changed mid-year and applied to old entries, and retainers quietly overrun. A sound tracker logs raw time once, applies the client's rounding rule in a separate column, looks the rate up with the most specific match winning (person and project, then project, then client, then default) and freezes it on the entry so later rate changes do not rewrite history, and stamps each entry with an invoice number when it is billed, so unbilled work is simply billable entries with no invoice number. Time arithmetic needs care: durations from start and end times are fractions of a day (multiply by 24 for hours), and times crossing midnight need MOD(end - start, 1). Rounding up to a 15-minute increment is CEILING(hours, 0.25).
</context>
<task>
Build the tracker in {{tool}}.
<work>
{{work}}
</work>
<rules>
{{rules}}
</rules>
1. Layout: tabs for Log (one row per time entry), Clients and Projects (with billing type, rate, currency, retainer hours, budget), Rates (if rates vary by person or date), Summary (client by month), and Invoice Prep.
2. Time log: Date, Person, Client, Project (dropdown filtered by client where the tool allows), Task, Start, End or Duration, Hours `=MOD(End - Start, 1) * 24` or the typed duration, Billable (Y or N, defaulting from the task type), Billed Hours with the rounding rule, Rate, Amount `=IF(Billable="Y", BilledHours * Rate, 0)`, Invoice No. Give each formula.
3. Rates: the lookup that picks the most specific rate (XLOOKUP or INDEX/MATCH with fallbacks), with effective dates if rates change, and a note to paste the rate as a value at month end (or use the date-based lookup) so history does not change.
4. Monthly summary: per client and month: total hours, billable hours, amount, unbilled amount (billable with blank Invoice No), and utilisation (billable over total) per person, using SUMIFS between the month's first and last days.
5. Retainers and fixed fees: hours used against the retainer each month with remaining and overrun flags; for fixed fees, hours spent and the effective hourly rate.
6. Invoice Prep: for a chosen client and month, the list of billable unbilled entries (FILTER in Excel 365 and Google Sheets) grouped by project with totals, ready to copy to an invoice; then the step of writing the invoice number back to those rows.
7. Checks: entries with no client or project, end before start, more than a set number of hours in a day per person, billable entries on non-billable projects, and totals that reconcile between Log and Summary.
</task>
<constraints>
- Rates, rounding increments and retainer sizes live in settings tabs, not in formulas.
- Keep raw time and rounded billed time in separate columns.
- Use functions available in {{tool}}; give an alternative where Excel and Google Sheets differ.
- Do not advise on what to charge; use the user's rates.
- Test the formulas on three sample entries, including one across midnight and one rounded up, and show the results.
</constraints>
<output_format>
## Layout
## Time log
Table: Column | Input or formula.
## Rates
## Monthly summary
Code blocks for the SUMIFS formulas.
## Retainers and fixed fees
## Checks
End with the worked example table: Entry | Raw hours | Billed hours | Rate | Amount.
</output_format>可替换内容
- {{tool}}
- {{work}}
- {{rules}}
分类与标签
办公效率、工时记录、电子表格公式、开票准备、自由职业