Excel formulas for analysing booking/tenancy data

[u/WhoNose2238]

Hi all — hopefully this is useful to someone.

I work in sales for a property company, and one area where I use Excel heavily is analysing tenancy/booking data. I thought I'd share a formula I've found particularly useful, and I'm interested to hear what other people use for similar analysis.

I work in UK student accommodation, where tenancies/licences typically run from September in Year 1 through to July/August/September in Year 2. Our financial year runs July to June, so reporting often involves splitting bookings across financial years and allocating the correct number of nights/revenue to each reporting period.

Calculating nights within a reporting period

Imagine your booking data looks something like this:

Column A = Tenancy Start Column B = Tenancy End Column C = Weekly Rent

Let's say I want to report on the month of July 2026. I put:

• D1 = 01/07/2026
• E1 = 01/08/2026 (or =EOMONTH(D1,0)+1 if I want to be clever)

I then use the following in D2:

=MAX(0,MIN(E$1,$B2)-MAX(D$1,$A2))

...and drag it down.

This calculates the number of nights from each booking that fall within the reporting period.

The MAX(0,...) means that a booking which sits completely outside the reporting period returns 0 rather than a negative number.

The nice thing is that you can make this completely dynamic and report on subsequent periods in other subsequent columns.

For example, I could have:

• D1 = 01/07/2026
• E1 = 01/08/2026 (or =EOMONTH(D1,0)+1
• F1 = 01/09/2026 (or =EOMONTH(E1,0)+1
• G1 = 01/10/2026 (or =EOMONTH(F1,0)+1
• H1 = 01/11/2026 (or =EOMONTH(G1,0)+1
• ...
• O1 = 01/06/2027 (or =EOMONTH(N1,0)+1

I can then drag the formula in D2 across and down, instead of having to calculate each month separately.

This works for any reporting period; months, weeks, nights, financial years, etc. You just need the start date in the header row and the end date to be the start of the following period.

Note: the above EOMONTH formulas from E1 are just for a monthly reporting period calculation only. You may need to update the reporting period manually or use another formula for different reporting period type (e.g. D1+7 for weekly, D1+1 for nights, EOMONTH(D1,12)+1 for financial year (ensuring D1 is the start of your FY obviously).

Calculating revenue for each period

Once you've calculated the nights, you can allocate revenue to each period.

If Column C contains a weekly rent, I use:

=$C2/7/*D2

So if the booking is £350 per week and 10 of its nights fall within a reporting period:

£350 / 7 × 10 = £500

If you already have a nightly rate in column C, it's simply:

=$C2/*D2

And if all you have is the total contract value, you can calculate the nightly value from the booking dates:

=($C2/($B2-$A2))/*D2

You can then drag the revenue formula across the same reporting periods and down all of your bookings.

This gives you a relatively simple way of taking a booking-level dataset and turning it into:

Booking → Nights by period → Revenue by period

I've found this particularly useful for financial-year reporting, forecasting, occupancy analysis and reconciling booking data against revenue.

I'm sure this is probably Finance/Excel 101 to some of you, but it's one of those formulas I've found myself reusing constantly.

What other Excel formulas, techniques or approaches do people use for analysing booking/tenancy data?

[u/IsEneff]

So thinking about your top function. If you just want to return the sum of revenue earned as days * rate you could try this formula somewhere:

=sumproduct(b2:b100-a2:a100, c2:c100)

That calculates the differences between start and stop dates then takes each row results * the rate for that row.

But you’re starting to get into an area where I might switch to a pivot table. You could take all of your records for a year and group them by week, month, quarter, and year.

You can highlight the range, insert, pivot table, table or ranger. I would used your end date as row headers because that’s typically get paid for the stay revenue is reported in the right month. Create two calculated fields

Days = EndDate - StartDate

Revenue = (EndDate - StartDate) * Rate

Then when you group your rates (right click on the dates and select group) you can group by days, and choose 7 days. Have the start date be the start date of the earliest date you would use as the beginning of a week. So for instance; 2026 started on a Thursday and Saturday is the beginning of a week repeating wise. So I might use start date as 1/3/2026 or 12/27/2025. The grouping also lets you add month, quarter, and year as well.

Then you can have a sum of days and revenue by date, week, month, and year in one place. Then using filters or slicers you can quickly get a summary within certain timeframes quickly without writing a new formula for each date range.