Travel itinerary template for Excel: plan the trip and the budget

In short: enter the departure and return dates once, then one row per item with date, time, activity, place, category, booking reference and cost. The trip day is =A9-$D$3+1, the costs per category come from SUMIF, and a budget sheet compares planned, booked and paid amounts. No currency symbol, so it works for any trip.

Before you start: calculating with dates, SUMIF and absolute references.

Try it now

In an itinerary, "day 3" is often easier to read than a date. Work out the trip day from the departure date.

Task In B2, work out the trip day: the departure date in F1 is day 1, the next day is day 2 and so on. The formula is filled down to B8.

	A	B	C	D	E	F
1	Date	Day	Plan		Depart	14 May 2027
2	14 May 2027		Flight out
3	14 May 2027		Check in
4	14 May 2027		Dinner
5	15 May 2027		Walking tour
6	15 May 2027		Tram
7	16 May 2027		Day trip
8	17 May 2027		Flight home

Download the travel itinerary template

The file has the trip details at the top with nights, days and a countdown, room for 200 plan items in an Excel table, trip day numbers, red rows for dates outside the trip, a budget sheet with planned, booked, paid and still to pay per category, a sample city break and an instructions sheet.

How the itinerary is laid out

Cell or column	Contents	Input or formula
D2 to D5	Trip, depart, return, travelers	Inputs
F2, F3	Nights, days	=D4-D3 and =F2+1
F4	Days to go	=MAX(0,D3-TODAY())
F5	Total cost	=SUM(Plan[Cost])
H2 to H5	Budget, left, per person, per person per day	From the Budget sheet and the totals
A, C to I	Date, time, activity, place, category, booking ref, cost, paid	Inputs, category and paid from drop-downs
B	Trip day	=IF(OR(A9="",$D$3=""),"",A9-$D$3+1)

Dates are shown as ddd d mmm, such as Fri 14 May, which reads the same in the US and the UK. Times use the h:mm format, so type them with a colon. The plan is an Excel table called Plan, and the filter buttons in the header sort by date and time with two clicks.

Two conditional formatting rules help you read the plan: rows whose date falls outside the trip turn red, and every second trip day gets a light grey band, so the days stand apart. Both use the trip day or the dates in D3 and D4, which is why those references carry dollar signs.

Trip day and nights: calculating with dates

A date in Excel is a whole number of days, so one date minus another is the number of days between them. That gives the nights straight away: return minus depart. The trip day adds 1, because the departure day is day 1, not day 0. The countdown in F4 uses TODAY and never drops below zero thanks to MAX. More in the lesson on date functions, and why $D$3 needs its dollar signs in the lesson on absolute references.

Travel budget template: costs by category

Each item has a category: Transport, Lodging, Food, Activities or Other. SUMIF adds up the costs of each category, and because the criteria is a cell, one formula covers all of them.

Task In G2, calculate the cost of the category in F2. The formula is filled down to G6.

	A	B	C	D	E	F	G
1	Item		Category	Cost		Category	Total
2	Hotel		Lodging	540		Transport
3	Flight		Transport	480		Lodging
4	Dinner		Food	70		Food
5	Walking tour		Activities	40		Activities
6	Tram		Transport	12		Other
7	Day trip		Activities	60
8	Dinner with fado		Food	110
9	Travel insurance		Other	25
10	Taxi to the airport		Transport	35

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

The Budget sheet has the planned amount per category as an input. Booked is =SUMIF(Plan[Category],$A5,Plan[Cost]), left is planned minus booked and turns red when it goes below zero. Paid uses SUMIFS with a second condition, Plan[Paid],"Yes", so you can see what is still to pay before you leave. The total budget feeds H2 on the itinerary.

Cost per person per day

For a group trip, the useful number is what each person pays per day. Divide the total by the travelers and by the trip days, and round to cents with ROUND.

Task In G5, calculate the cost per person per trip day, rounded to 2 decimal places. The departure and return days both count as trip days.

	A	B	C	D	E	F	G
1	Item			Cost		Travelers	2
2	Hotel			540		Depart	14 May 2027
3	Flight			480		Return	17 May 2027
4	Dinner			70
5	Walking tour			40		Per person per day
6	Tram			12
7	Day trip			60
8	Dinner with fado			110

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

In the template H4 and H5 do the same with the totals at the top, and stay empty while the number of travelers is missing, instead of showing #DIV/0!.

Trip planning ideas

• Several destinations: add a column for the city and filter by it, or keep one sheet per stop.
• Foreign currency: keep every cost in your home currency and convert as you type, or add a column with the amount in local currency and one with the exchange rate.
• Shared costs: add a column for who paid, then SUMIF per person shows who owes whom.
• Printing: select the table and set it as the print area under Page Layout; the sheet is already set to landscape.

To see the trip in a year view next to holidays and other plans, combine it with the Excel calendar. Business travel costs can go into the expense tracker as well.

Common mistakes

Dates typed as text

"14.5." or "May 14th" may stay text, and the trip day shows #VALUE!. Type dates in a form Excel recognizes, such as 14 May 2027.

Costs counted twice

If the flight home is included in the return fare, enter it with cost 0 so it shows in the plan without adding to the total, as in the sample.

Category spelled differently

"Hotel" does not match the category Lodging, and the cost misses the budget. Pick categories from the drop-down.

Itinerary formulas in German Excel

English	German
SUMIF, SUMIFS, SUM	SUMMEWENN, SUMMEWENNS, SUMME
TODAY, MAX, ROUND	HEUTE, MAX, RUNDEN
IF, OR, N	WENN, ODER, N

Which Excel versions support this?

Every function on this page, Excel tables, drop-down lists and conditional formatting work in all current versions of Excel, including Excel for Mac and Excel for the web.

FAQ