Spreadsheets

Mileage log template in Excel for business trips

Create a mileage log in Excel with start and end odometer, purpose, per-km value in a settings cell and a business versus personal split, with an example.

Editorial illustration for Mileage log template in Excel for business trips

A mileage log in Excel records the date, start odometer, end odometer, purpose and place for every trip, then calculates kilometres, business versus personal use and the reimbursement value at a per-km rate you keep in one settings cell. It takes a minute a day, and it is far easier than rebuilding a month from memory.

What columns does the mileage log need?

  • A: Date
  • B: Start odometer, in km
  • C: End odometer, in km
  • D: Km for the trip, calculated
  • E: Purpose, such as client visit, site inspection or collection
  • F: Client or site
  • G: Type - Business or Personal, from a dropdown
  • H: Reimbursement value, calculated
  • I: Odometer gap check, calculated

Put the per-km value in a Settings tab, cell B1. The rate comes from your employer's policy or your own agreement, so type it in and keep it out of the formulas.

Which formulas should I use?

  • D2, kilometres: =C2-B2
  • H2, reimbursement value: =IF(G2="Business", D2*Settings!$B$1, 0)
  • I3, gap check: =IF(B3=C2, "", "Gap")

The gap check catches the most common error. Each trip should start where the last one ended. If the cell says Gap, you rode somewhere without logging it, so add a Personal row for the missing distance. That keeps the odometer story continuous, which is what makes a log believable.

How do I get the monthly totals?

On a Summary tab, put the first day of the month in B1 and use these:

  • Business km: =SUMIFS(Log!$D$2:$D$500, Log!$G$2:$G$500, "Business", Log!$A$2:$A$500, ">="&B1, Log!$A$2:$A$500, "<="&EOMONTH(B1,0))
  • Personal km: the same formula with Personal in place of Business
  • Business share: =B3/(B3+B4), with B3 and B4 holding the two results
  • Reimbursement value: =SUMIFS(Log!$H$2:$H$500, Log!$A$2:$A$500, ">="&B1, Log!$A$2:$A$500, "<="&EOMONTH(B1,0))

A field sales executive's example

Say a field sales executive on a two-wheeler logs three days, with a pretend rate of ₹4 per km.

DateStartEndKmTypeValue
3 Nov12,40012,45252Business₹208
3 Nov12,45212,47018Personal₹0
4 Nov12,47012,53868Business₹272
5 Nov12,53812,59052Business₹208

Business km are 52 + 68 + 52 = 172, personal is 18, and the total of 190 matches 12,590 - 12,400. The reimbursement value is 172 x 4 = ₹688, and the business share is 172 / 190, about 90.5 percent.

How do I keep the log up to date?

  1. Photograph the odometer at the start of the first trip each day and after the last.
  2. Enter the day's trips every evening, while you still remember the clients.
  3. Write a purpose that a stranger would understand, such as site visit for installation, not just visit.
  4. Review the Gap column on the last day of the month.

What if I use the vehicle for both work and home?

Most people do. Log every trip, not just the work ones. A day that starts at home, goes to two client sites and returns is one Business row for each leg that was for work and one Personal row for the ride home, if that is how your employer wants it. If your organisation has a rule about the commute from home, such as counting only distance beyond it, ask them how it should be recorded and write the rule in a Notes cell on the Settings tab. Two minutes of clarity now saves an argument later.

Mistakes to avoid

  • Rounding every trip to a neat number; odometer readings should look like readings.
  • Logging Business for a trip that also included personal errands without splitting it.
  • Changing the per-km value mid-month without noting the date.

Fuel and service bills for the same vehicle can be tracked alongside this log; importing your card and bank statements into SpendixAI will bring those payments in under a fuel category so you can compare them with your kilometres.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports