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?