Getting started

Before, during and after: finding every cost of a trip in your statements

Find every cost of a trip in your card statements, including charges that land before you leave and after you return, with a euro example and a tag formula.

Editorial illustration for Before, during and after: finding every cost of a trip in your statements

A trip does not cost what the days away cost. Some money leaves your account weeks before departure, some during the trip and some a fortnight after you are home. Sort your card rows into those three phases and the real total appears, usually higher than the figure you remembered.

What are the three phases?

Before: flights, accommodation booked in advance, travel insurance, a new bag. During: everything bought from the day you leave to the day you return, including the final hotel payment taken at checkout. After: late-posting charges, deposits released, refunds and any card fee that appears on the next statement. Download the statements for all three periods, not only the month of the trip.

How do I tag the rows?

  1. Paste all rows into one sheet with Date in A, Description in B, Amount in C and Phase in D.
  2. Enter Before, During or After in column D for each trip row, and leave unrelated rows empty.
  3. Add a column E called Trip and type the name you chose, such as Autumn trip, for every row that belongs.
  4. Total with =SUMIF($E$2:$E$400,"Autumn trip",$C$2:$C$400).
  5. Split by phase with =SUMIFS($C$2:$C$400,$E$2:$E$400,"Autumn trip",$D$2:$D$400,"Before").

Refunds should carry a negative amount so they reduce the total automatically. If your export shows them as positive credits, put a minus sign in front when you tag them.

What does a worked example look like?

PhaseWhat appearsAmount
BeforeFlights €348.00 and insurance €41.00€389.00
DuringHotel balance €520.00, food €412.30, trains €96.40€1,028.70
AfterCar park charge posted late €22.50, deposit refund -€60.00-€37.50
Trip totalAll three phases together€1,380.20

The third row of the table needs a plain explanation: 22.50 minus 60.00 is minus 37.50, so the after phase reduces the total. Add them all, 389.00 plus 1,028.70 minus 37.50, and you get €1,380.20.

Why not just use the dates I travelled?

Because a date filter drops rows at both ends. A filter for the travel days would have caught only the during phase, €1,028.70, and understated the trip by about a quarter. Tagging each row by hand is slower, but it is the only way to include a hotel paid in July for a trip taken in October.

How do I know I have found everything?

  • Search the descriptions for the airline, hotel and booking site names you used.
  • Look at the three weeks after you got back for charges from places you visited.
  • Check every card in the household, since a partner's card may have paid for part of the journey.
  • Compare your total with the figure you expected, and look hard at any difference.

What about cash I withdrew for the trip?

A cash withdrawal appears as one row on your card or account statement, so tag it to the trip. You will not know what the cash bought without notes, and that is fine. Count the whole withdrawal as trip spending when you take it, and if some of it comes home unspent, do not subtract it unless you paid it back into the account. A short note such as "kept 40 for next time" in the sheet avoids confusion later.

A tidy way to finish

Once the total is stable, divide it by the number of travel days to compare trips. Save the tagged sheet with the trip name. For the next booking you can start from this real record, with its early deposit, its late fee and its refund, and plan the cash flow before the first payment leaves the account.

SPENDIXAI JOURNAL

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

Explore your reports