If your card's statement closes in the middle of your trip, the trip sits on two statements and neither shows the full cost. Pull both, keep only the rows between the trip's start and end dates, add the two subtotals, and divide by days and by people for the numbers you actually want. The whole job is arithmetic on a short list.
Why does the billing cycle matter?
A card statement covers a period set by the bank, which rarely lines up with the calendar month and almost never with your trip. Suppose you travelled for ten days from the 12th to the 21st and the cycle closes on the 15th. The first four days land on one statement and the last six on the next, so each statement alone shows a partial picture. Look for the statement period printed at the top of each document and note it before you start.
How do I combine two statements?
- Download both statements as CSV or a spreadsheet, plus any other account you used for the trip.
- Paste the rows into one sheet: Date in A, Description in B, Amount in C, Category in D.
- Put the trip start in G1 and the trip end in G2 as real dates.
- Total with =SUMIFS($C$2:$C$300,$A$2:$A$300,">="&$G$1,$A$2:$A$300,"<="&$G$2).
- Check each subtotal separately by adding the statement name in column E and using SUMIFS with that criterion too.
| Statement | Trip dates covered | Subtotal |
|---|---|---|
| First, closing on the 15th | 12th to 15th | C$538.40 |
| Second, closing next month | 16th to 21st | C$1,017.35 |
| Whole trip | 12th to 21st | C$1,555.75 |
What is the cost per day and per person?
The trip lasted ten days counting both the start and end dates, so use =G2-G1+1 for the day count. C$1,555.75 divided by 10 is C$155.58 a day. If three people travelled and one card paid for everything, divide by three: C$1,555.75 divided by 3 is C$518.58 each, rounded. If the others sent you money, subtract what they sent first, as the repaid amount does not belong in your cost.
Which categories matter on a road trip?
- Fuel: the rows from petrol stations along the route.
- Lodging: motels and rentals, often paid on arrival.
- Food: roadside meals, groceries and coffee, which add up as small rows.
- Park entry and activities.
- Tolls, parking and any repairs.
Tagging the categories shows which part of the trip you can control. Fuel may be fixed by distance. Food is where the rows pile up.
How can I make the next trip easier?
Use one card for the whole trip if you can, so the cost sits on a single statement and one cycle. Put the trip dates in your phone calendar so you can find them quickly later. When a charge arrives that you do not recognise, look at its date against the trip window and the description before you question it.
What if I used two cards on the road?
Then you have up to four statements to combine, two per card, and the same method still works. Paste all the rows into one sheet and add a column for the card name so you can total each separately. The extra column also protects you from paying twice for the same item, for example when a motel puts a hold on one card and the final charge lands on another. Compare the card subtotals with the figure you get from the date filter on the combined list; they should match.
Before you close the sheet
- Both statements are in the sheet.
- The date cells are real dates, not text.
- Late postings are checked after the next cycle.
- The figure is divided by days and by people.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


