Calculate Overlapping Nights between reservations and a report date range

AppSheet AppSheet Q&A
, , ,
1

Hello!, I’m trying to calculate the number of overlapping nights between reservation periods and a reporting period in my AppSheet app, but I’m running into issues because the relevant date fields are in different tables.

I have two tables in my app:

  1. Reservations (registro_reservas):

    • Contains [f_checkin] and [f_checkout] dates.

  2. Reports (reporte_economico):

    • Contains [desde_reporte] (start date) and [hasta_reporte] (end date) for each report.
    • Also includes a virtual column (e.g., [AllReservas]) that lists relevant reservations.

I want to calculate the number of overlapping nights between a reservation and a report’s period. For example:

  • Scenario 1:

    • Reservation: Jan 25, 2025 to Feb 2, 2025 (8 nights)
    • Report: Jan 1, 2025 to Jan 31, 2025
    • Expected overlap: 6 nights (Jan 25–31)

  • Scenario 2:

    • Reservation: Dec 28, 2024 to Jan 5, 2025 (8 nights)
    • Report: Jan 1, 2025 to Jan 31, 2025
    • Expected overlap: 4 nights (Jan 1–5)

I tried this formula to calculate overlapping nights:

MAX(
0,
MIN([f_checkout], [hasta_reporte]) - MAX([f_checkin], [desde_reporte])
)

It works if all fields are in one table, but my reservation dates and report dates are in different tables. I need a solution that lets each report (with its own dates) calculate overlapping nights for its reservations.

How can I compute the overlapping nights for each reservation relative to a report’s period in AppSheet?

I’m doing this because i need to calculate the percentage of occupation of a few rental properties.!

Any advice or recommended approaches (e.g., using a join table or another method) would be greatly appreciated!

2

There is an INTERSECT() function. You can obtain the the two lists of Dates and use INTERSECT() to get the list that is common between them.

I hope this helps!

1 Like