It's Friday afternoon and you're piecing together the week's work. The schedule shows the visits you planned. Crew messages say which ones happened. Last week's spreadsheet has the report format, so you copy it and start replacing the numbers.
Picture a small lawn care business serving Orlando and nearby neighborhoods. This is an illustrative example, not a client story. The owner needs to know what was completed and what still needs attention before planning the next week. Start with those two questions. Leave the charts until you can trust the underlying visit records.
Decide what the report should help you do
Start with a completed-visit count and a short list of unresolved work. Keep those views separate. The count describes activity during a period; the open list tells you what to act on now, including older visits that still need attention.
For the count, choose an explicit period and use the actual completion date. A Monday-through-Sunday week works if it fits your operation. A report prepared Friday afternoon is only a partial week, so label its cutoff time. Don't compare it with a full previous week as if both covered the same amount of time.
For the open list, include overdue visits, reschedules waiting for agreement and work whose completion hasn't been confirmed. Give each item an owner and a next action. Keep canceled visits identifiable so they don't look like work that's still owed.
Count visits, not messages
Keep one record for each planned service visit, with a reference that stays the same when the date changes. A regular customer's next weekly visit gets its own reference. A message about a visit updates that record; it doesn't create another completed job.
A first version needs a visit reference, a customer reference, planned date, actual completion date and status. Add a short reason for an unresolved item, its next action and the person responsible. A link back to the original job record is enough when someone needs more detail.
If a Tuesday mow moves to Thursday, update that visit and retain its original date in a note or history. Count it as completed on Thursday only after completion is confirmed. If a customer cancels, record the cancellation instead of deleting the visit to make the totals look tidy.
Blank dates need attention. A row marked completed with no completion date can't be placed reliably in the weekly count. Put it on a data-check list until someone confirms the date. Treating missing information as zero hides a problem the report should expose.
Build a summary from the records you already keep
If your scheduling software already produces these views, check its definitions and use them. Creating a parallel spreadsheet can leave you maintaining two different versions of the week.
For a business already working in Excel, a PivotTable is one option. It groups records and produces a summary. Microsoft's PivotTable guide covers creating one in Excel for the web. For a completed-visit report, use status and completion date as filters, service type as a row field if you track it, and the visit reference as a counted value. Set the calculation to Count explicitly.
Check for duplicate references first. Counting records won't fix a visit entered twice. Use an Excel table as the source so added rows are included when the PivotTable refreshes; Microsoft's guide distinguishes this from a fixed cell range that may need extending.
Refresh the summary before reviewing it. Microsoft's refresh instructions include a manual Refresh action for the web version. Don't assume your setup updates itself. Record when the source was last updated as well as when the report was prepared. A report generated today from Tuesday's export still describes Tuesday's information.
Keep the trial small and the sharing narrow
Try a few fictional rows first: a completed visit, a rescheduled one, a cancellation and a row missing its completion date. Include a duplicate deliberately. Check each result against the source rows before using the report for real decisions.
Microsoft offers free web apps, including Excel, with a Microsoft account. That gives you a way to try a small sample without buying desktop software. The PivotTable guide says recommended PivotTables require a Microsoft 365 subscription; this example uses manual setup. Keep real customer records in the accounts and storage your business has approved.
Share only what the reader needs. A crew lead may need unresolved visits assigned to the crew, while the owner reviews the wider report. Leave payment details, gate codes and unrelated customer notes out. Filtering a worksheet isn't a substitute for controlling who can open the underlying file.
After the review, look at the open list again. Did someone confirm a missing completion date or arrange an overdue visit? If nobody used the report, ask what information was missing before adding more fields. If the source is reliable and the summary is useful, a small integration could later prepare it for review. If dates and statuses are still unclear, settle those rules first. AI isn't required for counting visits or listing unresolved work.
The weekly reporting example shows how this could become a focused project. Bring the report you currently assemble and the decision you need it to support; we can discuss a manageable first change.