Use a nights-stayed Google Sheets tracker when someone leaves a hotel stay early. Log each night, mark who occupied it, divide that night's room cost by the occupants, and record payments separately. The sheet then shows who owes money and who should be reimbursed without forcing the group into an equal split.

Turns out, the hard part isn't the formula. It's agreeing whether an early departure changes that person's share, especially when the hotel keeps the original booking charge. Put that rule in writing before the trip.

For a $200 room shared by three people on nights 1 and 2, then two people on nights 3 and 4, each person on the first two nights has a raw share of $66.6667. Each remaining guest has a raw share of $100 on nights 3 and 4. The two full-stay guests owe $333.3333 each, and the early leaver owes $133.3333. Round only at the end so the group collects exactly $800.00.

Choose the rule before anyone checks in

A nights-stayed split often fits an early departure because it follows actual occupancy. It isn't the only reasonable method, though.

Split rule How it works Use it when
Equal split Divide the complete booking cost evenly among the group. Everyone agrees to share the reservation equally.
Nights-stayed split Divide each night's cost by the people staying that night. Occupancy changes during the trip.
Reservation commitment Keep an agreed share or fee with the person who leaves. The booking is nonrefundable or the group agreed to share that risk.

The hotel bill and the group's reimbursement rule are separate. A hotel may charge the same amount after someone leaves, so the remaining guests could face a higher nightly share unless the group has another agreement.

You can combine rules. Use nights stayed for ordinary room use, then add an agreed nonrefundable charge to the departing person's total. Record that adjustment separately instead of changing old occupancy rows.

Create the Google Sheets workbook

Make a spreadsheet with these tabs:

Tab Purpose
Occupancy One row per night, with costs, occupants, and automatic nightly shares.
Summary One row per person, showing nights, total owed, credited payments, and balance.
Costs Optional record for one-time fees, credits, deposits, and receipts.

One row, one night, one set of occupants. It sounds almost too simple, but that layout makes an early checkout easy to see and audit.

Use row 1 for headers and row 2 onward for data. Name the file something recognizable, such as Hotel Reimbursement Tracker.

Set up the Occupancy tab

Use the following columns for a group of up to six people. Replace the placeholder names with the actual names in row 1.

Column Header or content
A Night date
B Nightly room cost
C:H One column per person, with 1 if they stayed and 0 if they did not
I Total occupants
J:O One calculated share column per person

Enter the date for the night, not necessarily the checkout date. If someone checks out the morning after night 2, mark that person as 0 on night 3.

Use a dropdown containing 0 and 1 for the occupancy cells. It prevents entries such as yes, Y, and stayed from breaking the formulas.

In I2, enter:

=SUM(C2:H2)

In J2, the share column for the person in C1, enter:

=IF($I2=0,0,IF(C2=1,$B2/$I2,0))

Copy I2 down for every night. Copy J2 across the other share columns and then down. The person reference will move from C2 to D2, E2, and so on, while the cost and occupant total stay in columns B and I.

If you have four people, use C:F, set I2 to =SUM(C2:F2), and use share columns J:M. Don't leave unused person columns in the middle of the working range.

Format column B and the share columns as currency. Format column A as a date. Keep the formulas visible enough that another person can review them.

Build the Summary tab

Create one row per person. Keep the same person order used on the Occupancy tab.

Column Header Entry type
A Name Manual
B Nights stayed Formula
C Nightly share Formula
D Other assigned costs Manual or linked allocation
E Raw total Formula
F Rounding adjustment Manual
G Final share Formula
H Credited payments Manual
I Balance Formula
J Status Formula

For the person in occupancy column C and share column J, use these formulas in row 2:

B2: =SUM(Occupancy!C2:C)
C2: =SUMIF(Occupancy!C2:C,1,Occupancy!J2:J)
E2: =C2+D2
G2: =ROUND(E2+F2,2)
I2: =G2-H2
J2: =IF(ROUND(I2,2)=0,"Settled",IF(I2>0,"Owes","Is owed"))

Because the share formula already returns zero when someone is absent, =SUM(Occupancy!J2:J) also works in C2. The SUMIF version makes the occupancy condition more obvious.

For the next person, move both references one column to the right. For example, the person in Occupancy column D uses =SUM(Occupancy!D2:D) for nights and =SUMIF(Occupancy!D2:D,1,Occupancy!K2:K) for the nightly share.

Enter 0 in the Other assigned costs column when there are no extra charges. Use Credited payments for money that already reduces a person's balance, such as a hotel payment or a reimbursement sent to the booking payer.

A positive balance means the person still owes money. A negative balance means that person paid more than their share and should receive money.

Handle cents without losing a penny

Don't round every nightly formula to two decimals. That can leave the group short or overcharged by a few cents.

Use the Rounding adjustment column after the raw totals are calculated. In the four-night example, the final allocation could look like this:

Person Nights Raw nightly share Rounding adjustment Final share
Person A 4 $333.3333 $0.01 $333.34
Person B 2 $133.3333 $0.00 $133.33
Person C 4 $333.3333 $0.00 $333.33
Total $800.0000 $0.01 $800.00

Choose one clear rule for the leftover cent and write it in the sheet notes. The person who paid the hotel does not automatically receive the rounding adjustment.

Add a simple audit cell to the Summary tab:

=ROUND(SUM(G2:G)-SUM(Occupancy!B2:B)-SUM(D2:D),2)

The result should be 0.00 after all adjustments. If it isn't, check the receipt total, assigned costs, and rounding column before requesting payment.

Track taxes, fees, deposits, and credits

Enter the amount you actually intend to split. If the hotel receipt provides nightly room charges with taxes, use the nightly amount that matches your group's agreed cost basis.

Keep one-time items visible. A separate Costs tab can use these columns:

Column Header
A Date
B Description
C Amount
D Paid by
E Allocation rule
F Receipt note or link
Receipt item Tracker treatment
Nightly room charge or tax Add the amount for that night in column B.
One-time hotel fee Record it in Costs, then assign each person's agreed amount in Summary column D.
Refund or booking credit Record it as a negative cost or apply it to the related receipt line.
Deposit Keep it separate until you know whether it is returned or retained.
Early-departure or cancellation charge Add it separately and state who agreed to cover it.

A fixed fee can be divided equally, by nights stayed, or by another rule. None of those choices is automatic. Write down the choice, then enter the resulting person-level amounts in column D.

If rooms have different prices or quality, use a separate Occupancy block for each room. Don't pool a suite and a standard room into one nightly total unless the group has agreed to that split.

Use this workflow during the trip

  1. Before booking: List the names, planned nights, room assignments, and split rule. Decide how you will handle a no-show, early checkout, refund, and one-time fees.

  2. Before check-in: Add one row for each planned night. Enter the expected nightly cost and mark the expected occupants. Leave the formulas in place.

  3. During the stay: Update the occupancy row as plans change. If someone leaves after night 2, keep the first two 1 values and enter 0 for that person on later nights.

  4. After the final checkout: Replace estimates with the actual receipt amounts. Add credits, fees, and retained deposits on the Costs tab instead of hiding them in a nightly line.

  5. Before requesting money: Check the Summary balance, apply any one-cent adjustment, and confirm that allocated totals match the receipt. Review who paid the hotel.

  6. After payment: Add each credited payment, confirm the balance, and set the status to Settled when the remaining amount rounds to zero.

A clear request can be short:

The hotel total is $[total]. Your final share is $[share], and the sheet credits you $[paid]. Your remaining balance is $[balance]. I attached the receipt and used the nights-stayed rule we agreed on.

Keep the wording factual. The sheet should show the math without turning the message into an argument.

Share the sheet without creating formula problems

Give edit access only to people who need to enter occupancy or payments. Use Viewer access for people who only need to inspect the record and Commenter access for questions or proposed changes.

Protect the formula ranges through Data > Protect sheets and ranges. Leave the date, cost, occupancy, extra-cost, and payment cells editable. Protect the totals and status columns.

To be honest, one editor is often enough for a small trip. A treasurer or booking payer can maintain the structure while everyone else reviews the same version.

Keep the hotel receipt with the sheet or link it from the Costs tab. Don't place full card numbers or unrelated personal information in the workbook. Use File > Version history > See version history if someone changes a formula or disputes an earlier total.

Know when a spreadsheet is enough

A Google Sheet fits a single booking, a small group, and a split rule that everyone understands. It gives the group a visible record and separates tracking from the actual payment method.

A payment or expense app may be more convenient when the trip includes many unrelated expenses or repeated reminders. It still won't decide whether an early departure should pay for reserved nights. Set that rule first, then use whichever tool makes the records easiest to review.

Fix the common failure points

Problem Better fix
One total is divided by the number of travelers. Use one row per night and update the occupant count.
Someone types text into a 1 or 0 cell. Add a dropdown with only 0 and 1.
A formula gets overwritten. Protect formula ranges and keep input cells visually distinct.
Every nightly share is rounded. Keep raw formulas unrounded and use one final adjustment.
The hotel payer is forgotten. Enter that person's credited payment in Summary.
A departure rule changes halfway through the trip. Record the new agreement and apply it consistently.
A fixed fee is buried in one night's room cost. Put it on the Costs tab and assign it separately.

FAQ

Is a nights-stayed split always fair?

No. It is a usage-based method, not a universal rule. It works well when people pay for the nights they occupy, while a reservation commitment may make more sense when someone agreed to share a nonrefundable booking.

What if the hotel keeps the full charge after someone leaves?

Use the actual hotel cost in the tracker. If the group agreed that the departing person still covers part of the unused reservation, add that amount as a separate assigned cost instead of changing the occupancy history.

Can I use the tracker for a vacation rental?

Yes. Replace the nightly room cost with the rental's nightly allocation and use the Costs tab for cleaning fees, service fees, deposits, and refunds. Agree separately on whether fixed fees are split equally or based on nights stayed.

How should I handle partial nights or no-shows?

Write the rule before the trip. For a partial night, mark occupancy according to the agreed cutoff; for a no-show, use 0 if that person is excluded from the group split. If the booking cost cannot change, record any agreed reservation share separately.

What if people stay in different rooms?

Use separate cost and occupancy blocks for each room. A person in a more expensive room shouldn't be charged the same amount by default unless the group has agreed to pool the rooms.

Create the three tabs, enter four dummy nights, and confirm that the final shares add to the receipt total. Then replace the sample names and dates before sharing the file.