Pro-rating number of nights

[Hillside_Hostel]

I have a 4-room B&B, and I’m created a base (“Stats” base) that shows that stats of my B&B (i.e occupancy rate, occupied nights etc).

I linked that to a “Bookings” based where there is a separate column for check-in and check-out dates. They’re also linked by month

I have a guest who booked from February 26 to March 26. How do I pro-rate that entry so that 3 days will be counted in Feb, and 26 days will be counted in March?

[Brennan_Ward]

I’m sure there are other ways to do this, but I think the quickest way to accomplish this without breaking your existing structure would simply be to split up the bookings that straddle different months. Then all of your lookups and rollups in the Months table would calculate correctly

So, if someone booked from June 23 to July 5, it might look something like this:

[Russell_Findlay]

I think trickier than it may initially seem

This thread on finding all dates from start and finish dates may be useful

You could get from there to your answer by using date formulae, look up fields or automations alongside a dates table.

It may be more easily analysed in excel where there are easy to find formulae for the above.

@ScottWorld had some suggestions using make.com

@David_MacDougall1 i believe had a solution.

Best of luck

[ScottWorld]

Hmmm… yeah, this is a tricky one because of the limitations of Airtable. I like @Brennan_Ward’s idea of splitting up the bookings into different months.

If you wanted to use an external tool like Make.com, you would need to setup a loop to cycle through all the dates in between the start date & the end date, and then apply the daily rate for each day within that loop.

This would probably take me a few hours to set it up, so I wouldn’t be able to guide you through this here without being hired as a consultant on your job.